VBA Vlookup another sheet

[lotto009]

Dear Friend
How to edit code VBA for the VBA Vlookup another sheet please see table and ad visor

Thank you
lotto Beginner VBA
Sheet1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]item[/TD]
[TD]Detail[/TD]
[TD]Age[/TD]
[TD]School name[/TD]
[TD]Mother[/TD]
[TD]Country[/TD]
[TD]parent[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]david[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]T
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]job[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]steep[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]art[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Sheet2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]item[/TD]
[TD]detail[/TD]
[TD]School name[/TD]
[TD]teacher[/TD]
[TD]parent[/TD]
[TD]village[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]david[/TD]
[TD]BNK[/TD]
[TD]U[/TD]
[TD]P[/TD]
[TD]12[/TD]
[TD]VN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]job[/TD]
[TD]STK[/TD]
[TD]I[/TD]
[TD]O[/TD]
[TD]23[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]steep[/TD]
[TD]MONG[/TD]
[TD]K[/TD]
[TD]I[/TD]
[TD]45[/TD]
[TD]JAPAN[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]art[/TD]
[TD]KIWI[/TD]
[TD]L[/TD]
[TD]Q[/TD]
[TD]66[/TD]
[TD]Thai[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trebor76]

Hi lotto009,

What sheet / column(s) do you want to look up from from which sheet / column(s)? Is it the three missing fields (columns) from Sheet1 you've shown here? Why does it have to be VBA?

Robert

 

[lotto009]

Dear Trebor76
I need used to VBA code because many columns and many sheets (vlookup by formula is fine for me I can do)
Thank you
Biginner

 

[Trebor76]

Though you didn't answer all my questions see if this is what you're after (assumes data is in columns A to G):

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim wsOutput   As Worksheet
    Dim wsSource   As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsOutput = Sheets("Sheet1") 'Sheet name for the following VBA to fill in
    Set wsSource = Sheets("Sheet2") 'Sheet name containing completed data for VLOOKUP

    lngLastRow = wsOutput.Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With wsOutput
        'Formula for School Name
        With .Range("D2:D" & lngLastRow)
            .Formula = "=VLOOKUP(A2,'" & CStr(wsSource.Name) & "'!A:G,3,FALSE)"
            .Value = .Value 'Convert above formula to a value.  Comment out or remove if you want the formula to remain
        End With
        'Formula for Country
        With .Range("F2:F" & lngLastRow)
            .Formula = "=VLOOKUP(A2,'" & CStr(wsSource.Name) & "'!A:G,7,FALSE)"
            .Value = .Value 'Convert above formula to a value.  Comment out or remove if you want the formula to remain
        End With
         'Formula for Parent
        With .Range("G2:G" & lngLastRow)
            .Formula = "=VLOOKUP(A2,'" & CStr(wsSource.Name) & "'!A:G,5,FALSE)"
            .Value = .Value 'Convert above formula to a value.  Comment out or remove if you want the formula to remain
        End With
    End With
                  
    Set wsOutput = Nothing
    Set wsSource = Nothing
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[lotto009]

WOWW is WORK
Thank you for you kindly Khun Trebor76 to support I will learn your code and adept to my file
Thank you krab
lotto 's Biginner vba

 

[Trebor76]

You're welcome. I'm glad we were able to provide you with a solution