merge sheets based on common id with VBA

[Guest]

Hi
I need to loop throu Sheet1, read id in col A and mydata in col B - find the
matching id in Sheet2 col A and copy mydata into col C of Sheet2.
Sheet1 has few rows, Sheet2 many
Any help much appreciated!
Ulf

 

[Guest]

Use the Vlookup worksheet function in column C of Sheet2. You can replace
the formula with its results after.

With worksheets("Sheet2").Range("C2:C500")
.Formula = "=Vlookup(A2,Sheet1!A:B,2,False)"
.formula = .Value
End With

 

[Guest]

Thank you Tom
Unfortunately I can't make it work, results in: #NAME?. What am I doing wrong
/ulf

Sub MergeClubMemb()
' add member data for some clubs to main sheet with all clubs

Dim WSmain As Worksheet
Dim WSdata As Worksheet

Set WSmain = Sheet40 ' all clubs, id in col B
Set WSdata = Sheet42 ' some clubs with member data, id in col A, data
in B

With WSmain.Range("H2:H500")
.Formula = "=Vlookup(WSdata.Range(A:B),2,False)"
.Formula = .Value
End With

End Sub

 

[Tom Ogilvy]

You are mixing VBA inside your Excel formula. The formula puts in vlookup
literally how you have written it.

A. Excel doesn't know what WSdata.Range(A:B) is - that is you #Name error
you should have Sheet42!A:B instead.
B. You left out the first argument to vlookup.