Logical AND not working for checking Duplicates in VBA If Statement

[gimmick18]

Hi Guys,

Been digging around looking for a way of capturing duplicates in rows of a Worksheet. I have two columns "E" and "B" where I need to jump out of the loop (LoopOut) if BOTH of them are the same. If I use the code below with a single instance of "Cells(rw."E") = Tag_Number.Value, then it captures duplicates in Column E without issue. If however I try to use the AND logical statement, it doesn't capture anything. It's quite happy to push through the entire For/Next loop and miss any duplicates.

The values in the referencing (Tag_Number.Value, and WON.Value) are coming from a Form that allows data entry, and this code runs under the "Submit" button. So the process is simple; enter all the details in on a Form then check if there are any duplicates. I just can't get the code below to check if duplicates exist across multiple columns.

What am I doing wrong in terms of synatax, for the IF statement?

Thanks in advance for any support the gurus can give.
Regards,
Kevin


' Duplicate Check
Dim W_Num As String
Dim W_Type As String
Dim T_Num As String
Dim rw As Integer

T_Num = Tag_Number
W_Num = WON
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

For rw = 4 To LastRow
If (Cells(rw, "E") = Tag_Number.Value) And (Cells(rw, "B") = WON.Value) Then GoTo LoopOut
Next rw

 

[Fluff]

What happens if you just check the values in col B?
Do you exit the loop?

 

[gimmick18]

Hi Fluff!

Ah!....actually NO. If i modify to just check the "B" column....it fails to check it;

'Duplicate Check
Dim W_Num As String
Dim W_Type As String
Dim T_Num As String
Dim rw As Integer

T_Num = Tag_Number
W_Num = WON
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

For rw = 4 To LastRow
If Cells(rw, "B") = WON.Value Then GoTo LoopOut
Next rw

This doesn't capture the duplicate. Interestingly; the data in the "B" column is just a number (eg. 40112248). Should I have defined W_Num as an Integer rather than a string because it is just grabbing numbers? Hadn't realised that it was the "B" that was failing to check for duplicated. Column "E" works fine.

Kevin

 

[Fluff]

Are the col B numbers whole numbers, or can you have decimals?

 

[gimmick18]

Hi Fluff.......mystery solved, and I'm properly dumb!

I had set up the variable in DIM differently to how I was then setting their values further down (spelling; T_Type rather than W_Type in one instance). I was then using the Form values (WON_Type.Value) rather than the newly set up variable "W_Type".

So all in all, crap coding!

But your question over the WON caused me to focus in on that, and see the error in referencing.

Thanks v v much for the quick responses.

 

[Fluff]

Glad you sorted it & thanks for the feedback.