Hi,

Sorry to take up your time but I think I have found the issue

I have used & when I should of used And

Well it seems to work now.


"winnie123" wrote:

Hi,

I am struggling with loops again, just can not get this right. can you
please assist me once again.

I have the following piece of code which checks the customer and product
with a range to see if that combination already exist, thanks to Joel.
I have now realised that I need to enter a qty for price breaks so the loop
will need to check, customer, product and qty.

I had this code

With historyWks
Customer = Range("D11")
Product = Range("D9")
Found = False

With .Columns("C")
Set C = .Find(what:=Customer, LookIn:=xlValues, _
lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
'check column E for product
If C.Offset(0, 2) = 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

So based on my own logic I thought if I would need to add the new criteria
of Qty
as follows


With historyWks
Customer = Range("D11")
Product = Range("D9")
Qty = Range("D14") 'THIS IS A NEW LINE'
Found = False

With .Columns("C")
Set C = .Find(what:=Customer, LookIn:=xlValues, _
lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
'check column E for product
If C.Offset(0, 2) = Product & C.Offset(0, 5) = Qty Then 'THIS LINE IS
CHANGED'
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


Appreciate your help.

Thank you

Elaine