Hello everyone, I'm new here. I need some helps from you guys on how to lookup 2 columns from different sheets using VBA.
In the image below is a combination of 2 different workbooks, on top is when I use VBA and the bottom is when I use VLOOKUP formula manually. I need to look for 2 columns which are C and T from every sheets. I need to make the top image become like the bottom image using VBA.
This is my VLOOKUP formula:
=VLOOKUP($A2,'01'!$A$2:$T$144,3,FALSE) -- 01 is the sheet name and 3 is for Column C
=VLOOKUP($A2,'01'!$A$2:$T$144,20,FALSE) -- 01 is the sheet name and 20 is for Column T
This is my code:
Sub test() Dim lastRow As Long Dim j As Byte j = 1 ' 3(space between columns) x 12(sheets) For i = 3 To (3 * 12) Step 3 With Sheets("Summary") With .Cells(2, i).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 1, 1) .Formula = "=VLOOKUP($A2,'" & Format$(j, "00") & "'!$A$2:$T$144," & IIf(i Mod 6 = 3, 3, 20) & ",FALSE)" .Value = .Value End With End With j = j + 1 Next End Sub
How do I fix the code? Thanks in advance