Need help with lookup? or if? command [Solved/Closed]

Report
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
-
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
-
Hi Everyone,
Can someone help me with a command or macro that will look up the price of a product in a country from a reference worksheet and then fill in that price in another results sheet.

Reference Sheet is arranged as:

Product name Country 1 Country2
Product A 1.50 .25
Product B 3.25 .75

Results/Original sheet- Need to find results for Column C

A B C
Product Name Country Local Price
Product A Country 1 1.50
Product A Country 2 .25
Product B Country 1 3.25
Product B Country 2 .75

There's about 5,000 rows to look up so any assistance is greatly appreciated!

Thank you



2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
the data is in sheet1 from A1 to c3

now try to macro and confirm wheter you got what you want (in columns G etc.

Sub test()
Dim ra As Range, r1 As Range, ca As Range, c1 As Range, unq As Range
Worksheets("sheet1").Activate
Set ra = Range(Range("a2"), Range("A2").End(xlDown))
Set r1 = Range(Range("B1"), Range("B1").End(xlToRight))

For Each ca In ra
For Each c1 In r1
Set unq = Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
'MsgBox c1
'MsgBox ca
unq = ca
unq.Offset(0, 1) = c1
unq.Offset(0, 2) = Application.Intersect(Columns(c1.Column), Rows(ca.Row))
Next c1
Next ca

End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013

Thank you! I'm going to test it.
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013

Hi Venkat,
I got it to work once I modified for my spreadsheet. Your help is genuinely appreciated!