VBA finding end of array within function

  • I created a macro within Excel with the below code, however I noticed that using the entire range gave me a #VALUE error. I re-recorded the macro and only used a range up to 10 which output the correct results. Dissecting the code, I narrowed down the exact number where the array stops working at R308. When I change the array to R309 and < the output is #VALUE for the output array. My data range is to R579. The interpolation is from a data set in ascending order. Seems arbitrary but my lack of VBA experience precedes me.

        Selection.FormulaArray = "=csplinea(R3C1:R308C1,R3C2:R308C2,R3C7:R308C7)"

    Once I can figure this out, is there a way to find the end (such as a lastRow) within the formula's arrays when outputting the final array? I have data sets that vary in length and would like the automation to work regardless of range.

    Much appreciated,

  • Re: VBA array #VALUE error from function and finding end of array within function

    Zach - please remember to use code tags as per forum rules.

    Bit hard to say without seeing your data and the code for the function. Do you have any error values in your input cells?

  • Re: VBA array #VALUE error from function and finding end of array within function

    Du-oh. Please disregard the first portion of my question. I found the error within the dataset (duplicate cell values). The code still applies to my second question.

  • Re: VBA array #VALUE error from function and finding end of array within function

    Under your name in any post is a link "I agreed to these rules".

    To find the last row in column A, you can use

    lastrow=range("A" & rows.count).end(xlup).row

    And then substitute that in your formula for your hardcoded value. Is that what you mean?

  • Re: VBA finding end of array within function

    Thank you. I think I'm part way there. The array stops at R579 but R3:R13543 is the interpolated range of data generated from R3:R579. I can run the macro and generate the appropriate array interp (without the lastRow command and using the specific ranges), but since the interpolated range will always be dependent upon different data sets, so will always the array, so therefore must be produced from the interpolation of the last row of Column A. I guess I'm not sure how to hardcode the value in the spline function.

    lastRow = range("A" & Rows.Count).End(xlUp).Row
        range("H3:H" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C2:R579C3,R3C7:R13543C7)"
        range("I3:I" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C3:R579C3,R3C7:R13543C7)"
        range("J3:J" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C4:R579C4,R3C7:R13543C7)"
        range("K3:K" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C5:R579C5,R3C7:R13543C7)"
  • Re: VBA finding end of array within function

    Thank you for the clarification. That works but I'm still having the problem of the array stopping at the end of the set and not interpolating to the end of the array (C3,R3C7:R" & lastrow &"). I coded the array with the last row command which then output an array of all zeros and stopped at the set end, not at the end of the array. Something I've never seen before is that it also kicks me back into VBA, but there's no error or bug statement.

    When I look at the function formula in Excel, it has the array matching the data set end. Could it be something to do with the original command stating that lastRow is dependent upon "A" which consequently is R579, the end of the array? The all zero thing also confuses me but I think that is because the array cannot have a lastrow command as it is in it's current definition. Without the lastrow command coded into the array function (that is C3,R3C7:R1354357), the interpolation works beautifully, it just doesn't carry through.

  • Re: VBA finding end of array within function


    I believe the first sub routine somehow got "mangled" since I had to go back to my original worksheet, but the spline sub routine up to now is in there. The first subroutine is to find the maximum value in range "A" and use that as the end value for the spline sub routine.

  • Re: VBA finding end of array within function

    Zach - there's no code in your file.

    This is a guess at what you're asking

    range("H3:H" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastrow & "C1,R3C2:R" & lastrow & "C3,R3C7:R" & Range("A" & lastrow)-range("A3")+1 &"C7)"
  • Re: VBA finding end of array within function


    It still not continuing through to end of row. I don't know why Ozgrid won't allow me to upload the file with the array and cspline modules, so this attachment only has the macro code - i believe you have an earlier iteration with those modules which are needed. I've also included the macro coding below:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!