Cell Comparsion (a bug?)

[kaon]

Hi all,

I am wondering if this is a bug for excel.

Here is the (simplified and ugly) code: (Problem details right afte
the code)


Sub test()
Dim i As Integer, j As Integer, k As Integer, h As Integer, l A
Integer
Dim poNo As String

k = 5

For h = 1 To Worksheets("Raw Data").UsedRange.Columns.Count
If Cells(1, h).Value = "Po No" Then
Exit For
End If
Next h

For j = 1 To Worksheets("Raw Data").UsedRange.Columns.Count
If Cells(1, j).Value = "Je Period" Then
Exit For
End If
Next j
End Sub

I have 2 sheets (Settings, Raw Data)
When I add the following line just before "End Sub" and run:
Msgbox _
Worksheets("Settings").Cells(66,h).Value = _
Worksheets("Raw Data").Cells(149, j).Value

Msgbox with "False" is appeared.

Later on, I changed the line to:
Msgbox _
Worksheets("Settings").Cells(66,h).Value & " " &
Worksheets("Raw Data").Cells(149, j).Value

Msgbox with "123456 123456" is appeared.

And further I changed the line to :
Msgbox _
Len(Worksheets("Settings").Cells(66,h).Value) & " " &
Len(Worksheets("Raw Data").Cells(149, j).Value)

And

Msgbox Instr(1, _
Worksheets("Settings").Cells(66,h).Value, _
Worksheets("Raw Data").Cells(149, j).Value)

Result of former one: 6 6
Result of latter one: 1

So I am wondering why direct cell comparsion will give "false".

Thanks

 

[Harlan Grove]

I have 2 sheets (Settings, Raw Data)
When I add the following line just before "End Sub" and run:
Msgbox _
Worksheets("Settings").Cells(66,h).Value = _
Worksheets("Raw Data").Cells(149, j).Value

Msgbox with "False" is appeared.

Later on, I changed the line to:
Msgbox _
Worksheets("Settings").Cells(66,h).Value & " " &
Worksheets("Raw Data").Cells(149, j).Value

Msgbox with "123456 123456" is appeared.

....

What does the statement

MsgBox _
Application.WorksheetFunctions.IsNumber( _
Worksheets("Settings").Cells(66, h).Value) & " " _
& Application.WorksheetFunctions.IsNumber( _
Worksheets("Raw Data").Cells(149, j).Value)

return?

 

[kaon]

em.. I think this would give me "True True" coz I entered the numbe
manually. (just press 123456)

Interestingly, when I compare either one of them to a variable, say,

Dim PoNo as String

PoNo = Worksheets("Settings").Cells(66, h).Value
If Worksheets("Raw Data").Cells(149, j).Value = PoNo Then
'Blah blah blah
End if

It will run the statement inside the if-block!
but I cannot do DIRECT comparsion using =

 

[Harlan Grove]

em.. I think this would give me "True True" coz I entered the number
manually. (just press 123456)

....

OK, don't eliminate this as a possibility. But before you dismiss a very
likely explanation for why your code is fubar, consider that all the other
tests you've tried except for the equality comparison induce implicit number
to text conversion. Once both cells' values are converted to text, of course
they'll give the results you've already stated, but if one of them has
numeric type and the other string type, equality comparison *will* result in
FALSE. Just because you entered two 'numbers' manually doesn't mean one of
the two cells may not have number format Text.

Basic debugging concepts: assume nothing, and never trust the validity of
any inputs, especially not your own.

 

[Jerry W. Lewis]

Manually entering the number does not preclude the possibility that the
cell was preformatted as text (which can be induced implicitly by a cell
formula). As Harlan has pointed out, all of your posted results are
consistent with one value being 123456 (number) and the other one being
"123456" (string).

Minor correction to Harlan's suggestion: there is no "s" on the end of
Application.WorksheetFunction

Jerry