code for communicating between a table workbook an

Question

A couple of days ago I posted a question in order to find out how i could write a code that let me communicate between a function workbook and a input/table workbook. I was graciously helped by @DarthSpeedious to come up with a code that returned the correct value after computing in another workbook.

However, it only returned a single calculation. In order to try to loop the code that I was helped with the last time, I tried to create an array for one of the input variables to use as an counter for the loop process. Upon running the code, VBA keeps freezing up on me, and thus I have no way of debugging the code. I've tried several different ways of constructing both the array and the loop argument but none has been successful.

Can anyone please help me?

Public Sub Decompact()

    ' Gets input from another workbook
    Dim wb1 As Workbook                                                                             ' Declaring wb1 and wb2 as variable of type Workbook
    Dim wb2 As Workbook

    Set wb1 = Workbooks.Open("decompaction along exmpleline.xlsx")                                  'Note: In order to access data from another workbook, it should be open.
    Set wb2 = ThisWorkbook                                                                          'ThisWorkbook: refrence to the workbook having this code


    'Setting up array for y1
    last_row = Range("U3").End(xlDown).Row

    Dim y1()
    ReDim y1(last_row - 2, 1)

    'Storing values in array
    For i = 3 To last_row - 2
        y1(i, 0) = Range("U" & i + 2)
    Next


    'Looping decompaction
    For z = LBound(y1) To UBound(y1)

        wb1.Sheets("Shaly sst").Range("B3") = wb2.Sheets("Sheet1").Range("U" & i + 1)                   'Accept input of variable y1 in Ui and store it in cell B3 of book1
        wb1.Sheets("Shaly sst").Range("B2") = wb2.Sheets("Sheet1").Range("V" & i + 1)                   'Accept input of variable y2 in Vi and store it in cell B3 of book1

        wb2.Sheets("Sheet1").Range("AC" & i + 1) = wb1.Sheets("Shaly sst").Range("H3")                  'Output from book1 in H3 to a cell ACi in book2

     Next z

End Sub

Figure resource: https://db.tt/AxFG9snn

Answer 1

nvm, figured it out:

Sub Decompact3()
' Gets input from another workbook
Dim wb1 As Workbook                                                         ' Declaring wb1 and wb2 as variable of type Workbook
Dim wb2 As Workbook
Dim LastRow As Long

Set wb1 = Workbooks.Open("decompaction along exmpleline.xlsx")                                      'Note: In order to access data from another workbook, it should be open.
Set wb2 = ThisWorkbook                                                      'ThisWorkbook: refrence to the workbook having this code

    With wb2.Sheets("Sheet1")
        LastRow = .Range("U" & .Rows.Count).End(xlUp).Row
    End With

    For i = 3 To LastRow
        wb1.Sheets("Shaly sst").Range("B3") = wb2.Sheets("Sheet1").Cells(i, 21)               'Access value stored in cell A1 of sheet1 in book1 and stre it in cell A1 of book2
        wb1.Sheets("Shaly sst").Range("B2") = wb2.Sheets("Sheet1").Cells(i, 22)

        wb2.Sheets("Sheet1").Cells(i, 29) = wb1.Sheets("Shaly sst").Range("H3")        'Store the output (cell C1 of book2) in cell C1 of book1
    Next i

End Sub

Thanks again to DarthSpeedious for his help!