comparison fails with "left" function

[sailo]

Hejhej,

I try to compare two values for equality. While I definitly compare the same values, it fails when the left (or right function for that matter) is involved.

I created a minimal example to demonstrate my problem. I have also experiences a lot with adding ".value" and some other things, nothing seemed to work...

Option Explicit
Dim tmp As String
Sub testleft()
Cells(1, 1) = 123456
Cells(1, 3) = 123
tmp = Left(Cells(1, 1), 3)
' not equal:
If Cells(1, 3) = Left(Cells(1, 1), 3) Then
    Cells(5, 1) = "yes"
End If
' equal:
If Cells(1, 3) = tmp Then
    Cells(6, 1) = "yes"
End If
End Sub

Can someone explain to me why the first if-clause is not true and the second is? It is the same value! Why doesn't it work?

(I expect "yes" to be in both cells 5, 1 and 6, 1).

Thanks!

EDIT: Tested in Excel 2001 and 2007.

 

[VoG]

You are comparing a string with a number. Try

If CStr(Cells(1, 3).Value) = Left(Cells(1, 1).Value, 3) Then

 

[sailo]

Ok, that worked.
In the macro where I needed this, I tried changing the type of the variable I was using from string to long but that didn't help. Maybe I had to change the compared Cell-Value. I am sure that I can solve this now.

Thanks a lot!

 

[sailo]

Sorry, I have to bother again because I don't fully understand it yet:

In this part

If Cells(1, 3) = Left(Cells(1, 1), 3) Then
    Cells(5, 1) = "yes"
End If

Cells(1,3) is a number and not equal to the string "Left(..."
Converting it to a string with Cstr works here. Okay. I get that.

But here:

If Cells(1, 3) = tmp Then
    Cells(6, 1) = "yes"
End If

Cells(1,3) (a number) is equal to the variable tmp which is declared as a string!

That doesn't make sense, does it?

 

[Dryver14]

a string can consist of anything on the keyboard up to 255 charachters long it does not mean letters.

 

[VoG]

a string can consist of anything on the keyboard up to 255 charachters long it does not mean letters.

No, there is definitely something weird going on. In the first If it is necessary to convert the number to a string to compare with the second string. With the second If, which is essentially the reverse, it is not necessary to convert the number to a string. It is as if VBA does this coercion automatically but I don't understand why.