Convert Vlookup Formula to Macro

[Ian]

Hello
I have the following Formula that I have pasted into each Cell down in Column C,
so when a Part Number is entered into a Cell in Column A, the correct Part Description
for that Part Number is then looked up in the PARTNUMBERS worksheet, and
the correct Part Description is then automatically put into the cell in Column C.

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000,2,FALSE))

I found this Formula on a newsgroup and edited it to suit my worksheet so it probably
could be fine tuned further to suit my needs but it works ok.

What I would like to do is to convert this Formula into a Macro.
Can anyone help.

Cheers
Ian

 

[Rob van Gelder]

I find it strange that you're doing a lookup of A2 in a range which include
A2. It's going to find a match straight away.

Vlookup:
VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000,2,FALSE))
The above formula in plain english:
Find A2 in range PARTNUMBERS!$A$2:$A$1000 and return the value in the same
row of column 2 (ie. return the value in B2:B1000)
Most VLookups have the last argument as False, meaning exact match.


Code as requested:
Sub test()
With Range("C2")
If IsEmpty(Range("A2").Value) Then
.Value = ""
Else
.Value = Application.VLookup(Range("A2"), _
Range("PARTNUMBERS!$A$2:$B$1000"), 2, False)
End If
End With
End Sub

 

[Ian]

I find it strange that you're doing a lookup of A2 in a range which include
A2. It's going to find a match straight away.

Hi Rob
Thanks for your quick reply.
Each Part Number is entered into cells down in Column A in "sheet1", it then looks up
the Part Number and Part Description in "sheet2", (renamed to PARTNUMBERS)
between Cells A2 & B1000, and then the corresponding Part Description is entered
into Column C back in "sheet1".
So down Column A, we type the Part Numbers when they come in for repair, and we
will type in different Part Numbers in different Rows down Column A.

Code as requested:
Sub test()
With Range("C2")
If IsEmpty(Range("A2").Value) Then
.Value = ""
Else
.Value = Application.VLookup(Range("A2"), _
Range("PARTNUMBERS!$A$2:$B$1000"), 2, False)
End If
End With
End Sub

I pasted the above code, but didn't work, I obviously wasn't very clear in my original post.
The lines - " Range("A2") " and " Range("C2") " are obviously pointing to specific Cells, (A2 and C2),
how do I specify the complete Column A and Column C.

Thanks for your help
Ian