Posts by shane91

Re: Lookup multiple columns from different sheets in VBA

Please someone help me

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.

[ATTACH=CONFIG]69116[/ATTACH]

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