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.

range("H3:H308").Select
    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,
Zach

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

Apologies Stephen. Can you link me to the forum rules area?

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

[ATTACH=CONFIG]64191[/ATTACH]

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

forum.ozgrid.com/index.php?attachment/64206/

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:

Sub DepthSeries()
'
' finds maximum depth recorded and uses number to produce number series for spline array
'


'
    Sheet1.range("G1").FormulaR1C1 = "=MAX(C[-6])"
    range("G2").Select


    Call CSplineDepth1(Sheet1.range("G2"), "Depth", Sheet1.range("A3"), Sheet1.range("G1"), 1)


   
End Sub




Sub CSplineDepth1(StartCell As range, Header As String, FirstN As Integer, LastN As Integer, StepN As Integer)


' Integer version produces series to maximum depth for array interpolation


    Dim i As Integer ' Value
    Dim r As Integer ' row


    StartCell.Cells(1).Value = Header ' Cells(1) makes sure it only uses the first cell of passed-in range
                                        ' in case you pass in a multi-celled range for StartCell
    i = FirstN
    r = 1


    Application.ScreenUpdating = False ' Much faster, so the screen is not refreshed until all the values in place


    For i = FirstN To LastN Step StepN
        StartCell.Cells(1).Offset(r, 0).Value = i
        i = i
        r = r + 1
    Next i


    Application.ScreenUpdating = True
    
' Excel formula that produces array for DepthSeries
' Range("H3:H" & lastRow).FormulaArray = CSplineA($A$2:$A$578,B$2:B$578,$G$2:$G$12322)
' Range("I3:I" & lastRow).FormulaArray = CSplineA($A$2:$A$578,C$2:C$578,$G$2:$G$12322)
' Range("J3:J" & lastRow).FormulaArray = CSplineA($A$2:$A$578,D$2:D$578,$G$2:$G$12322)
' Range("K3:K" & lastRow).FormulaArray = CSplineA($A$2:$A$578,E$2:E$578,$G$2:$G$12322)
' below is vba code used to automate cspline function
    
 lastRow = range("A" & Rows.Count).End(xlUp).Row
 
    range("H3:H" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C2:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("I3:I" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C3:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("J3:J" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C4:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("K3:K" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C3:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    
End Sub ' CSplineDepth1