"Rob van Gelder" wrote in message ...
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