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

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

--
Regards,
Tom Ogilvy


"ulfb" wrote:

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

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" wrote:

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

--
Regards,
Tom Ogilvy


"ulfb" wrote:

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

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.

--
Regards,
Tom Ogilvy


"ulfb" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"ulfb" wrote:

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