Hi Gang

I have 2 sheets. In the first sheet I have 2 columns with data and I
want to fill in the 3rd column with a cost amount. I get this cost
amount using the "Part Num" on Sheet1 and finding it on Sheet2 in the
2nd column "Material Number". I need to return the 3rd column on
Sheet2 "Standard Price" into the "Cost" column on Sheet1.

The number of rows in Sheet1 will be variable and the same with Sheet2
as these change on a daily basis. I need to create a loop that
searches Sheet2 with a VLookup or something better that would return
the cost amount on a found part number to Sheet1.

Can someone please help me with this as I've been struggling with this
for a view days now with no luck. Below is some sample data.

Thanks
Andy


Sheet1
Reference Part Num Cost
C1 20900080
C2 20800170
C3 20800171
C4 20850361
C5 19B230595
C6 20800149

Sheet 2
Material Number Description Standard Price
19B230583G002 RECV'R/SYNTH ASM 4KHZ 631.91
19B230583G003 RECV'R/SYNTH ASM RV#02 102.72
19B230584G001 SYNTHESIZER BD. REV#02 0
19B230584G002 SYNTHESIZER BD. REV#02 0
19B230584G003 SYNTHESIZER BD. REV#04 443.58
6FX20014QA50 500 PPR SIEMENS OPTICAL 16660
6FX20014QB00 1000 PPR SIEMENS OPTICATL 16660
6FX20014QC50 ENCODER OPT. 6FX2001-4TL 16660
6FX20030SU12 SIGNALSTECKER 12 POL. 2650.08
6SE64000BP000AA0 MICROMASTER 4 2042.5
6SE64000SP000AA0 MICROMASTER 410 1878
6SE64001PB000AA0 MICROMASTER 4 18562

Part Nums in Sheet1 don't match Material Numbers in Sheet2 and never won't
match them unless you specify the rule of some special matching!

Regards,
Stefi

€žAndy€� ezt Ã*rta:

Hi Gang

I have 2 sheets. In the first sheet I have 2 columns with data and I
want to fill in the 3rd column with a cost amount. I get this cost
amount using the "Part Num" on Sheet1 and finding it on Sheet2 in the
2nd column "Material Number". I need to return the 3rd column on
Sheet2 "Standard Price" into the "Cost" column on Sheet1.

The number of rows in Sheet1 will be variable and the same with Sheet2
as these change on a daily basis. I need to create a loop that
searches Sheet2 with a VLookup or something better that would return
the cost amount on a found part number to Sheet1.

Can someone please help me with this as I've been struggling with this
for a view days now with no luck. Below is some sample data.

Thanks
Andy


Sheet1
Reference Part Num Cost
C1 20900080
C2 20800170
C3 20800171
C4 20850361
C5 19B230595
C6 20800149

Sheet 2
Material Number Description Standard Price
19B230583G002 RECV'R/SYNTH ASM 4KHZ 631.91
19B230583G003 RECV'R/SYNTH ASM RV#02 102.72
19B230584G001 SYNTHESIZER BD. REV#02 0
19B230584G002 SYNTHESIZER BD. REV#02 0
19B230584G003 SYNTHESIZER BD. REV#04 443.58
6FX20014QA50 500 PPR SIEMENS OPTICAL 16660
6FX20014QB00 1000 PPR SIEMENS OPTICATL 16660
6FX20014QC50 ENCODER OPT. 6FX2001-4TL 16660
6FX20030SU12 SIGNALSTECKER 12 POL. 2650.08
6SE64000BP000AA0 MICROMASTER 4 2042.5
6SE64000SP000AA0 MICROMASTER 410 1878
6SE64001PB000AA0 MICROMASTER 4 18562

HI Stefi

I was just showing some sample data. In fact there are a few thousand
rows. So there will be a match for 20900080 in Sheet2 somewhere. Can
you help me with the lookup code assuming that there will be a
match. :-)

Thanks
Andy

Sub test()
Worksheets("Sheet1").Activate
NoOfRows = Range("B" & Rows.Count).End(xlUp).Row
Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-2]:C,3,FALSE)"
Range("C2").AutoFill Destination:=Range("C2:C" & NoOfRows),
Type:=xlFillDefault
End Sub

Regards,
Stefi

€žAndy€� ezt Ã*rta:

HI Stefi

I was just showing some sample data. In fact there are a few thousand
rows. So there will be a match for 20900080 in Sheet2 somewhere. Can
you help me with the lookup code assuming that there will be a
match. :-)

Thanks
Andy

Hi Stefi

That's awesome! Works perfectly. Thanks!

One question. Is there any way you can tell if it doesn't find a
match when it is searching a particular value?

Regards,
Andy

One suggested change to the VLOOKUP to handle the error you get when a part
number is not found in the master list:

Range("C2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[-1],Sheet2!C[-2]:C,3,FALSE)),"NOT
FOUND",VLOOKUP(RC[-1],Sheet2!C[-2]:C,3,FALSE))"

I might have a missing parenthesis or two or a comma in the wrong place, but
you get the idea - you want to do something for those cases where you get the
"#N/A" errors. If you don't want to put "NOT FOUND" in, you can put $0.0 or
something else instead.

HTH,

Eric
-------------------------
If toast always lands butter-side down, and cats always land on their feet,
what happen if you strap toast on the back of a cat and drop it?
Steven Wright (1955 - )


"Stefi" wrote:

Sub test()
Worksheets("Sheet1").Activate
NoOfRows = Range("B" & Rows.Count).End(xlUp).Row
Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-2]:C,3,FALSE)"
Range("C2").AutoFill Destination:=Range("C2:C" & NoOfRows),
Type:=xlFillDefault
End Sub

Regards,
Stefi

€žAndy€� ezt Ã*rta:

HI Stefi

I was just showing some sample data. In fact there are a few thousand
rows. So there will be a match for 20900080 in Sheet2 somewhere. Can
you help me with the lookup code assuming that there will be a
match. :-)

Thanks
Andy

VLOOKUP returns #NA when the searched value is not found. If you want to
replace #NA by something else, follow Eric's suggestion!
You are welcome! Thanks for the feedback!
Stefi
Clicking the YES button will be appreciated.


€žAndy€� ezt Ã*rta:

Hi Stefi

That's awesome! Works perfectly. Thanks!

One question. Is there any way you can tell if it doesn't find a
match when it is searching a particular value?

Regards,
Andy