Need help converting FORMULA to MACRO that merges data with a common field.

[IrishMist1748]

I have the following formula (VLOOKUP) that compares 2 workbooks (Sheet1 on each) using column A as a common field(id) and merges the data putting the 'Price' info in Book2 in column C of Book1.

Book1:

Excel Workbook
	A	B
1	Item ID	Desc
2	91101	U.S. FLAG - BRASS W/GOLD PLATE & RHINESTONES PIN 2.5MM STONE
3	110115	PLAT BGT/PRIN DIA BRIDAL SET D1.00TW
4	110122	14KWHT HEART SHAPE PAVE DIA RING D.58TW
Sheet1

Book2:

Excel Workbook
	A	B
1	Item ID	Price
2	91101	$7.62;QTY
3	110115	$1,650.00;QTY
4	110122	$299.00;QTY
Sheet1

Merged data (column "C" contains formula):

Excel Workbook
	A	B	C
1	Item ID	Desc	Price
2	91101	U.S. FLAG - BRASS W/GOLD PLATE & RHINESTONES PIN 2.5MM STONE	$7.62;QTY
3	110115	PLAT BGT/PRIN DIA BRIDAL SET D1.00TW	$1,650.00;QTY
4	110122	14KWHT HEART SHAPE PAVE DIA RING D.58TW	$299.00;QTY
Sheet1

Formula:

=IF(COUNTIF([Book2.xls]Sheet1!$A$1:$A$10000,A1),VLOOKUP(A1,[Book2.xls]Sheet1!$A$1:$B$10000,2,FALSE),"")

My question is: How can I st this up as a macro?

Also, does someone know of a better way to accomplish this?

Thank you!

 

[TurboDieselOne]

Record a macro using the normal tools macros/record new set a something like CTRL + o for the keyboard shorcut

Then Edit the macro pasting something like

Dim i as interger
for i = 1 to 10000
Range(Ci)=IF(COUNTIF([Book2.xls]Sheet1!$A$1:$A$10000,Ai),VLOOKUP(Ai,[Book2.xls]Sheet1!$A$1:$B$10000,2,FALSE),"")
next

Just a guess but something along these lines then press ctrl + o on that spreadsheet and it should do the business

I think the Range notation is wrong but it should go
for Column C Row i
Cell Ci = IF(COUNTIF([Book2.xls]Sheet1!$A$1:$A$10000,Ai),VLOOKUP(Ai,[Book2.xls]Sheet1!$A$1:$B$10000,2,FALSE),"")
Next i untill it gets to number (or row in this case) (10000)

 

[IrishMist1748]

I tried modifying it as follows (see RED):

Merge2Sheets()

Dim i As interger
For i = 1 To 10000
Cell([COLOR=Red]C:i[/COLOR])=IF(COUNTIF([Book2.xls]Sheet1!$A$1:$A$10000,[COLOR=Red]A:i[/COLOR]),VLOOKUP([COLOR=Red]A:i[/COLOR],[Book2.xls]Sheet1!$A$1:$B$10000,2,FALSE),"")
Next

End Sub

But it did not work!

PLEASE, CAN SOMEONE HELP?

 

[IrishMist1748]

ANYONE?

OK, I changed it to this and now I am getting a error where it HIGHLIGHTS the IF statement on line 4 (Compile Error; Expected Expression):

Sub Merge2Sheets()

Dim i As interger
For i = 1 To 10000
CellCi=IF(COUNTIF([Book2.xls]Sheet1!$A$1:$A$10000,Ai),VLOOKUP(Ai,[Book2.xls]Sheet1!$A$1:$B$10000,2,FALSE),"")
Next

End Sub

THANK YOU!