Vlookup - vba - data validation list

[scottie_72]

Hi There.. (Again!)

I have three columns (C D E)

> Column C: Has Data validation list
> Column D: Is where I want the VLookup formula (in code) to retrieve the relevant info.
> Column E: Has a Quantity Value (Not really relevant as this is a manual entry)

I have done the below code - now it works but not automatically - If I put this in a Worksheet _Change procedure it comes up with an error - and also it doesn't work if cell "C" is blank.

Sub VLookup()


Dim ws As Worksheet, lRow As Long, moreInfo as Variant


Set ws = Sheets("OrderForm")
lRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
moreInfo = ws.Range("D" & lRow)


With ws
moreInfo = Application.WorksheetFunction.VLookup(Range("C" & lRow), Range("P_Table"), 2, 0)
.Range("D" & lRow).Value = moreInfo
End With
End Sub

Can anybody please help as its been driving me mad for a couple of days now!

Many Thanks in Advance