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.
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.
VBA Code:
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