Need help with lookup? or if? command

Solved/Closed
nycm
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
- Oct 28, 2013 at 07:20 PM
nycm
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
- Oct 31, 2013 at 05:43 PM
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

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Oct 30, 2013 at 12:10 AM
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
nycm
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013

Oct 30, 2013 at 09:38 AM
Thank you! I'm going to test it.
0
nycm
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013

Oct 31, 2013 at 05:43 PM
Hi Venkat,
I got it to work once I modified for my spreadsheet. Your help is genuinely appreciated!
0