Validate product and automatically fetch price

In sheet 2 I have a column for `product` and a column for `price`. In sheet 2 I have a column of validation for `product`. When I select for example `Pants`, `price` should automatically come in the next column. However, it should also be possible to write `price` manually, therefore I need to do this with VBA.

Skjermbildewfwqf.jpggssg.jpg


Can anybody help please?

It is not clear what column Product and Price are in for Sheet 1 so I will assumn column A and B respectively. The code placement will be within Sheet2 for the VBA Project.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Target.Offset(, 1) = Sheets("Sheet1").Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole).Offset(, 1)
End If

End Sub

Thanks for the reply. Is close to the target, but does not work 100%. Maybe I've done wrong? Attaching spreadsheets, maybe you can check this. Thank you in advance.

Of course it didn't work, I had bad information.

This goes in Order Form Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Target.Offset(, 1) = Sheets("Sheet2").Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole).Offset(, 1)
End If

End Sub

Thank you. You have made my day, I'm very happy. Again, thanks for all your help.

Hello again. I have a small question with regard to the worksheet. I can overwrite cells for the price, but can not overwrite cells for product. Very nice if I can edit both product and price without error "Object variable or With block variable not set." Thanks for all your help

Hi
I found a solution, used "On Error Resume Next" and everything seems very nice. Thanks again to you

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error Resume Next
Target.Offset(, 1) = Sheets("Sheet2").Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole).Offset(, 1)
End If

End Sub