Array Calculation Order Problem - Why I can't emulate something easily done in excel?

Question

I am trying to emulate something I have done easily in Excel in a VBA array, but I can't get it to run. I think it has to do with the nature of the calculation which depends on the order in which the rows are calculated. I will attach an excel worksheet with the VBA code, but will include it here as well.

This Simplified Example is a stand-in for a much larger array with about 50 rows and 80 columns of calculations. One particular column has a set of calculations that depend on the row's position relative to a selected row - if the row number is higher, one set of calculations apply that draw on the PREVIOUS row's value; if the row number is lower, another set of calculations apply that use the value in the NEXT row. This simplified example looks like this:

The three blue columns are from VBA. The next two white columns are the same calculations done in Excel (and they execute faster!).

You can see the second blue column has a set of values that are calculated upwards above line 4 and downwards below line 4. Basically there is an input in cell C2 called Select Row which determines which the 'middle' row is, and it enters the value input in cell C3 (46) in that row and then in each subsequent row it multiplies the PREVIOUS row's value by that same 46. Going the other way, for each row below the 'middle' row each line multiplies the NEXT row by 46. This is a stand-in for a bunch of other complex trig calculations. This is trivial for Excel, which doesn't seem to care which cell depends on what, it just returns the correct values, but it is fiendishly difficult in VBA - for me anyway.

I've been able to work this out by iterating the entire column of calculations enough times to fill in the values for the cells that depend on the NEXT row values, which are empty the first pass, since the NEXT row doesn't yet have a value. I may not have done this in the most efficient way, and if you have a better idea, I'd appreciate hearing about it.

But the real problem is that in another column I want to use the values that process returns. In the Excel version of the 3rd blue column each row in column F subtracts the value in that line in column E from the value in the NEXT line in column E (the first cell in F is highlighted yellow along with the two cells it is using). It does this regardless of where the line is relative to the Select Row input number.I can't make a version of this that works in my array in VBA.

I've tried a number of ways, but nothing I do seems to work. Somehow in cycling through the array the dependent column 3 never returns values and I get a Subscript Out Of Range error. I've tried a couple of strategies, neither has worked, and I'm out of ideas. I've attached the VBA code I've used:

Sub SimplifiedExample()

Dim myArray(1 To 10, 1 To 3) As Double
    'for this example the first column is just the index value i, the second is a calculation that depends on the row, and the third refers to another calculation based on the result of the second
Dim selectrow As Integer
    'represents an input value from a User Form, but just coded in here for this example
Dim valuefromAnotherArray As Double
    'represents something calculated elsewhere and referenced here

Dim n As Integer 'iteration counter
Dim i As Integer 'index counter
Dim x As Integer 'yet another counter!


    selectrow = Range("C2") 'user input
    valuefromAnotherArray = Range("C3") 'represents the result of a calculation elsewhere
    
'Because some of the rows depend on the value in the NEXT row which hasn't been calculated yet,
'the sub needs to be repeated as many times as there are rows < select row
'so there is an iteration counter and a normal row counter
For n = LBound(myArray) To selectrow
    For i = LBound(myArray) To UBound(myArray)
        myArray(i, 1) = i
        
        Select Case myArray(i, 1)
        'This bit does the up and down calculation that depends on the row's position relative to select row
            Case Is = selectrow
                myArray(i, 2) = valuefromAnotherArray
            Case Is > selectrow
                myArray(i, 2) = myArray(i - 1, 2) * valuefromAnotherArray 'represents a complex calculation
            Case Is < selectrow
                myArray(i, 2) = myArray(i + 1, 2) * valuefromAnotherArray
        End Select

        'Approach A - This doesn't work in this position. It throws a Subscript Out Of Range error.
        'myArray(i, 3) = myArray(i + 1, 2) - myArray(i, 2)
        
        Debug.Print myArray(i, 1), myArray(i, 2) ',myarray(i,3) doesn't work yet
        Cells(i + 5, 2).Value = myArray(i, 1)
        Cells(i + 5, 3).Value = myArray(i, 2)
        Cells(i + 5, 4).Value = myArray(i, 3) 'doesn't work
    Next i
Next n

'Approach B
'Tried adding another counter to cycle through the values produced above and actually use them in the rest of the array
'but throws another Subscript Out Of Range error, so it doesn't work here either
    'For x = LBound(myArray) To UBound(myArray)
    '    myArray(x, 3) = myArray(x + 1, 2) - myArray(x, 2)
    '    Cells(x + 5, 4).Value = myArray(x, 3)
    'Next x

 End Sub

Here's a link to the Excel file with the VBA in it. Link to Simplified Example in Excel I think there is probably a simpler way to do all of this than what I have shown here. I'd appreciate any help you can offer, and keep in mind I hope to integrate it into a 50 x 80 2D array. Thanks!

Answer 1

Wouldn't this accomplish the same:

EDIT for Column 3

Sub test()
    Dim ws As Worksheet
    Dim i As Long, startRow As Long, inputRow As Long
    Dim inputFromOtherArray As Double
    
    Set ws = ThisWorkbook.Worksheets("sheet_name")
    
    startRow = 15
    inputFromOtherArray = 46
    inputRow = 4
    With ws
        For i = 0 To 9
            .Cells(startRow + i, 3).Value = inputFromOtherArray ^ (Abs(inputRow - (i + 1)) + 1)
        Next i
        
        For i = 0 To 8
            .Cells(startRow + i, 4).Value = .Cells(startRow + i + 1, 3).Value - .Cells(startRow + i, 3).Value
        Next i
        
        .Cells(startRow + 9, 4).Value = -.Cells(startRow + i, 3).Value
    End With
End Sub