IF values not recognizing numbers

[Skrej]

I have the following code snippet which is supposed to check the values of some textboxes and dropdown boxes on a userform, in order to avoid duplicate entries.

'check for duplicates
rng = Sheet2.Cells(Rows.Count, "E").End(xlUp).Row
For Each MyCell In Sheet2.Range("E7:E" & rng)
If MyCell = Me.Reg3.Value And MyCell.Offset(0, 1).Value = Me.Reg4.Value And MyCell.Offset(0, 2).Value = Me.Reg5.Value Then
MsgBox "This training already exists for this staff member"
Exit Sub
End If

While doing some testing, I couldn't figure out why it wasn't picking up duplicate entries. Then, I discovered that if that those values in the E column were alphanumeric, the code worked as expected, and gave the warning about a duplicate entry, and exited the sub as expected. However, if the values were strictly numeric, I don't get the message, and the rest of the sub continues to run and enters a duplicate entry.

Ultimately, I'll need those values (which are employee ID #'s) to be only numbers, so any suggestions on how to tweak my code? I can't change the format of the employee ID numbers.

Why aren't those IF statements recognizing duplicate numbers?

Edit: I realized it might help to show what I'd declared my variables as, so here they are.

Dim cNum As Integer
Dim nextrow As Range
Dim MyCell As Range
Dim rng As Long

 

[Fluff]

Try

If MyCell = cdbl(Me.Reg3.Value)

or

If MyCell = clng(Me.Reg3.Value)

 

[Nine Zero]

What is MyCell referring to?

In the 2 line you state
For Each MyCell In Sheet2.Range("E7:E" & Rng)

Meaning MyCell is a Range

but in line 4 you forget to call the .Value of it on the first AND

MyCell = Me.Reg3.Value

'check for duplicates
rng = Sheet2.Cells(Rows.Count, "E").End(xlUp).Row
[COLOR=#ff0000][B]For Each MyCell In Sheet2.Range("E7:E" & rng)[/B][/COLOR]
If [COLOR=#ff0000][B]MyCell = Me.Reg3.Value[/B][/COLOR] And MyCell.Offset(0, 1).Value = Me.Reg4.Value And MyCell.Offset(0, 2).Value = Me.Reg5.Value Then
MsgBox "This training already exists for this staff member"
Exit Sub
End If

 

[Skrej]

Try

If MyCell = cdbl(Me.Reg3.Value)

or

If MyCell = clng(Me.Reg3.Value)

Thanks, I tried the 2nd one, which did the trick. however, I'm still not sure what the problem was with recognizing numbers versus mixed numbers and letters.

 

[Skrej]

What is MyCell referring to?

In the 2 line you state
For Each MyCell In Sheet2.Range("E7:E" & Rng)

Meaning MyCell is a Range

but in line 4 you forget to call the .Value of it on the first AND

MyCell = Me.Reg3.Value

'check for duplicates
rng = Sheet2.Cells(Rows.Count, "E").End(xlUp).Row
[COLOR=#ff0000][B]For Each MyCell In Sheet2.Range("E7:E" & rng)[/B][/COLOR]
If [COLOR=#ff0000][B]MyCell = Me.Reg3.Value[/B][/COLOR] And MyCell.Offset(0, 1).Value = Me.Reg4.Value And MyCell.Offset(0, 2).Value = Me.Reg5.Value Then
MsgBox "This training already exists for this staff member"
Exit Sub
End If

Yes, MyCell is checking individual cells in a dynamic range from E7 to the last entry in Col E. If I understand you correctly, you're saying line four should read:

If [B]MyCell.[COLOR=#ff0000]Value[/COLOR] = Me.Reg3.Value[/B] And MyCell.Offset(0, 1).Value = Me.Reg4.Value And MyCell.Offset(0, 2).Value = Me.Reg5.Value Then

I did try adding in that .Value, but it didn't change the results.

 

[Fluff]

All values in a text box are text (ie as string), so if you have a number you need to convert it from a string back to a number

 

[Skrej]

Ahh, I see. Thanks for the help, all.

 

[Fluff]

You're welcome