RIGHT function doesn't work when put within an IF statement-CodePudding

I want to create an IF condition using the RIGHT function. It would look up the 4 last digits within a cell and compare it to another cell, then perform actions if it's a match.

Here's a simplified version of the code I toyed with. The action to be performed in this experience is just to display the counter in a cell.

Public vCounter

Sub Counter()

vCounter = 0

Sheets.Add.Name = "Test"

'The cells the RIGHT function will operate from (A1, A2 and A3)
Sheets("Test").Range("A1") = "123"
Sheets("Test").Range("A2") = "456"
Sheets("Test").Range("A3") = "789"

'The cells the result of the RIGHT function will be compared to (B1, B2 and B3)
Sheets("Test").Range("B1") = "23"
Sheets("Test").Range("B2") = "456"
Sheets("Test").Range("B3") = "89"

'This cell (G3) shows the result of a RIGHT function, considering the
'last two digits in A1, as an experience; it works.
Sheets("Test").Range("G3") = Right(Sheets("Test").Cells(1, 1), 2)

For i = 1 To 3

'The RIGHT function considers the two last digits of, successively,
'A1, A2 and A3, and those are compared to, respectively, 
'B1, B2 and B3. For some reason, it doesn't work here.
    If Right(Sheets("Test").Cells(i, 1), 2) = Sheets("Test").Cells(i, 2) Then
        vCounter = vCounter   1
    End If
Next i

'This cell (E3) shows the counter, to test whether or not the If
'condition with the RIGHT function works. By changing the contents
'of the cells I compare between each other, I can check whether or
'not it counts correctly. 
Sheets("Test").Range("E3") = vCounter

End Sub

Here's what I get: The sheet that I get when I run this procedure

In conclusion, in this experience, the RIGHT function somehow doesn't work since the vCounter doesn't get to 2. It stays at 0, showing it doesn't count at all. I deduce from this result that the problem resides in the IF statement containing the RIGHT function. Maybe the For Loop has to do with it, but I doubt it.

Any thoughts?

CodePudding user response：

Even though you're writing string values to your sheet, Excel will automatically assume them to be numeric values, so when you read them back you will be getting values of type Double.

If you pass one of those Doubles through Right() though, it will return a String, and it's that comparison between String and Double which seems to be failing.

Some test code:

Sub Tester()
    
    Dim ws As Worksheet, v As Variant
    Set ws = ThisWorkbook.Worksheets("Test")
    
    ws.Range("A1").Value = "123"
    ws.Range("B1").Value = "23"
    
    Debug.Print TypeName(ws.Range("A1").Value), _
                TypeName(ws.Range("B1").Value)             '>> Double / Double
    
    Debug.Print TypeName(Right(ws.Range("A1").Value, 2))   '>> String (not Variant?)
    
    'Comparing values...
    Debug.Print Right(ws.Range("A1").Value, 2) = ws.Range("B1").Value '>> False (String vs Double)
    
    Debug.Print Right(ws.Range("A1").Value, 2) = CDbl(ws.Range("B1").Value) '>> True (why though?)

    Debug.Print Right(ws.Range("A1").Value, 2) = CStr(ws.Range("B1")) '>> True (String vs String)
    
End Sub

Note - I'd expect the first two value comparisons to be equivalent, but they give False then True. Anyone know why?