Vlookup in VBA inside a loop

[bajwali]

Hi

So new to vba but making some good progress however this one is kicking my back side a bit so if someone here could help would be great.

So i have data by month for multiple accounts and as the balance goes down or up, i want to look up the data by account over time. The monthly data is in one sheet of the workbook while i want to create a summary screen which only shows all the account names and then the balance by month.

Now the structure of the imported data is such that it comes in 5 columns every month

Column 1 : Some name
Column 2: Account name
Column 3: Balance
Column 4: Change in balance
Column 5: Text

I bring in this data by month as accounts vary by month. The vlookup i want to create then has to look into the different Columns with every loop.

Now the jump is simple if the first lookup is B:C the next one needs to be G:H and so on however how i can do that in a vlookup in vba is what is throwing me out.

Sub VL_Fill()

Dim nRow As Integer, nCol As Integer

Dim i As Integer, j As Integer, D As Integer, Sh As Worksheet

' First row has account names which another macro pulls and prints with the first row as a heading and making sure all accounts ever opened are captured.
Sheets("Aggregator").Select
Cells(2, 1).Select
Range(Selection, Selection.End(xlDown)).Select

nRow = Selection.Rows.Count
nCol = (Worksheets("Data").Cells(5, Columns.Count).End(xlToLeft).Column) / 5

Sheets("Aggregator").Select

Set Sh = ActiveWorkbook.Sheets("Data")
'This is where i need a lot of help first i need the vlookup to always look up account name in cell A2 to A & (nrows+1) second i need the Range to be dynamic so
'for the first vlookup it needs to be Row B:C but for the second vlookup i need it to be offset or moved by 3 columns over. How can i do that?


For j = 2 To nCol + 1
      For i = 2 To nRow + 1
      Cells(i, j) = Application.WorksheetFunction.VLookup("A" & i, Sh.Range("B:C"), 2, False)
    Next i
Next j

    
End Sub

 

[offthelip]

All you need to do is use the numerical method for addressing the columns , like this, I haven't tested it so I might have got it wrong.

     Cells(i, j) = Application.WorksheetFunction.VLookup("A" & i, Sh.Range(Columns(j + (j - 2) * 4), Columns(j + 1 + (j - 2) * 4)), 2, False)

 

[bajwali]

Hi @offthelip that seems to be the solution but the Sh.Range columns part of the code gives an error "Method 'Range' of Object'_worksheet' failed.

Is their any other way to get the range into the vlookup? or do you or anyone else know the issue?

Sh.Range(Columns(j + (j - 2) * 4), Columns(j + 1 + (j - 2) * 4))

 

[offthelip]

try this which avoids trying to address the entire column for the lookup:

With sh
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
End With
For j = 2 To nCol + 1
      For i = 2 To nRow + 1
      Cells(i, j) = Application.WorksheetFunction.VLookup("A" & i, sh.Range(Cells(lastrow, j + (j - 2) * 4), Cells(lastrow, j + 1 + (j - 2) * 4)), 2, False)
    Next i
Next j