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

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

You were real close, but no cigar.

from
If C.Offset(0, 2) = Product & C.Offset(0, 5) = Qty Then

to
If C.Offset(0, 2) = Product And C.Offset(0, 5) = Qty Then


the "&" is used to connect two string together like

A = "Boys"
B = "Girls"
c = A & "and" & B which gives the string "Boys and Girls"

The logic AND is used to connect two logicals

a = False and False which gives False
a = False and True which gives False
a = True and False which gives False
a = True and True which gives True

Your statement
If (C.Offset(0, 2) = Product) And (C.Offset(0, 5) = Qty) Then

This line is a combination of two equalities

C.Offset(0, 2) = Product will results in either TRUE or FALSE
C.Offset(0, 5) = Qty will result in either TRUE or FALSE

Now you want two check that both equalities are true
"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

Thanks Joel for the clarification and explanation.
Guess I will just have to have cigerette's :-)



"joel" wrote:

You were real close, but no cigar.

from
If C.Offset(0, 2) = Product & C.Offset(0, 5) = Qty Then

to
If C.Offset(0, 2) = Product And C.Offset(0, 5) = Qty Then


the "&" is used to connect two string together like

A = "Boys"
B = "Girls"
c = A & "and" & B which gives the string "Boys and Girls"

The logic AND is used to connect two logicals

a = False and False which gives False
a = False and True which gives False
a = True and False which gives False
a = True and True which gives True

Your statement
If (C.Offset(0, 2) = Product) And (C.Offset(0, 5) = Qty) Then

This line is a combination of two equalities

C.Offset(0, 2) = Product will results in either TRUE or FALSE
C.Offset(0, 5) = Qty will result in either TRUE or FALSE

Now you want two check that both equalities are true
"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