the code check column A for the customer. then check one column over
offset(0,1) for product. Finally it displays value in column E in the
message box moving over 4 columns from A offset(0,4).

Sub findcustomerProduct()

customer = "123"
Product = "Apple"
Found = False
With Worksheets("Data").Columns("A")
Set c = .Find(what:=customer, LookIn:=xlValues, _
lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'check column B for product
If c.ofset(0, 1) = Product Then
Found = True
Exit Do
End If
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

If Found = True Then
MsgBox ("customer and product combination already exist." & _
vbCrLf & _
"Price is (use value from the Data sheet column E : " & _
c.Offset(0, 4) & ")." & _
vbCrLf & _
"Do you wish to replace?")

End If


End Sub


"winnie123" wrote:

Asking for help again.

Not sure how to go about this but I have read different articles in this
forum and on the web but still cant get this sussed.

I have a sheet "Input" which I want to use for entering prices. it has
data is in column D
Customer name - selected via validation list
Customer No - uses lookup
Currency - uses lookup
Product - selected via validation list
price - input by user

I have the code that will copy the cells to another sheet (Data).

What I want to do is before that data is copied check to see if the Customer
and Product already exists.

If it exists need a msgbox to appear "customer and product combination
already exist. Price is (use value from the Data sheet column E) Do you wish
to replace.

If the user says yes then copy over existing record on Data sheet.
if the user says no then clear cells and exit sub.

I am using excel 2003.

Is this possible?

Thank you
Winnie