Comparing Text Contents of Cells

[Keith Norris]

Please tell me how to compare the contents of cells that contain text. If I
try to compare a cell that contains two identical strings, they never equal
each other. For example, if I try a statement similiar to the following:

If Cells(1,1) = Cells(2,1) Then
msgbox "They are equal"
End If

I have never been able to get this to result in a true condition meaning
They are equal is never shown in a messagebox when the two cells contain for
example EQUAL. I have also tried using the range object with no success. I
know this must be able to be done but I must not be using the correct syntax.

Please reply with the correct syntax and an example.

Thank you very much in advance!

 

[Luke M]

If Cells(1, 1).Value = Cells(2, 1).Value Then
MsgBox "They are equal"
End If

 

[Bernie Deitrick]

Keith,

It depends on what you actually want to compare: the text (displayed value) or the value
(underlying, unformatted value)

Sub TryNow()
If Cells(1, 1).Value = Cells(2, 1).Value Then
MsgBox "The values are equal"
Else
MsgBox Cells(1, 1).Value & " does not equal " & Cells(2, 1).Value
End If
If Cells(1, 1).Text = Cells(2, 1).Text Then
MsgBox "The text values are equal"
Else
MsgBox Cells(1, 1).Text & " does not equal " & Cells(2, 1).Text
End If
End Sub


HTH,
Bernie
MS Excel MVP

 

[Socko]

If you are sure that the content of a cell is equal in terms of string
or value, formating, formula, etc. then this may work... If you want
to just find out if they are equal in content string regardless of
letter case, you may use ...

If ucase$(Cells(1, 1).text) = ucase$(Cells(2, 1).text) Then
MsgBox "They are equal"
End If

If you jsut want to compare value then you may use the following:

If Cells(1, 1).Value = Cells(2, 1).Value Then
MsgBox "They are equal"
End If

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, and other resources
Please visit: http://socko.wordpress.com

 

[Bernie Deitrick]

Luke,

Value is the default property addressed when none is specified for range objects, so these two are
essentially identical,,though the first is the better programming practice:

If Cells(1, 1).Value = Cells(2, 1).Value Then
If Cells(1,1) = Cells(2,1) Then

HTH,
Bernie
MS Excel MVP