Error 2042

[magix]

Dear Guru,

I have this

Dim nameInput as String
nameInput = InputBox("Please specify the value of NAME that you want to
filter." "Specify State")
If nameInput = "" Then
Exit Sub
End If

For x = 1 To FinalRow
NameValue = range("AA" & x).Value

' check Name first
If (StrComp(NameValue, nameInput, 1) = 0) Then
...
...
Next x

In above code, when I run the macro, it showed error in StrComp, where when
I mouseover to NameValue, it showed the "NameValue = Error 2042".
WHY ?????

In Column AA, there are a list of Name, I just want to compare the input
name with the list of name in Column AA (Note that I have a huge list of DB)

Regards, Magix

What am i doing wrong here ?

 

[Bernie Deitrick]

magix,

This should work:

Sub TryNow()
Dim nameInput As String
Dim x As Long
Dim FinalRow As Long

'Other Stuff
nameInput = InputBox("Please specify the value " & _
"of NAME that you want to filter.", "Specify State")
If nameInput = "" Then
Exit Sub
End If

For x = 1 To FinalRow
NameValue = Range("AA" & x).Value
' check Name first
If (StrComp(NameValue, nameInput, 1) = 0) Then
MsgBox "Hello, the name is in row " & x
End If
Next x
End Sub


But better would be a non-stepping version:

Sub TryNow2()
Dim nameInput As String
Dim myCell As Range

nameInput = InputBox("Please specify the value " & _
"of NAME that you want to filter.", "Specify State")
If nameInput = "" Then
Exit Sub
End If

Set myCell = Range("AA:AA").Find(nameInput)

If Not myCell Is Nothing Then
MsgBox "Hello, the name is in cell " & myCell.Address
End If

End Sub

HTH,
Bernie
MS Excel MVP

 

[magix]

Hi Bernie,

Thanks for the code. But I don't see much different compared to mine code.
I would like to know why I got Error 2042. I understand that Error 2042 is
returning equip to #N/A, which mean that the field that I refer to has no
value (But in fact, there is a value)

So, in my code:

NameValue = range("AA" & x).Value
' check Name first
If (StrComp(NameValue, nameInput, 1) = 0) Then

when come to Strcomp function, it triggerred exception, because the
NameValue is Error 2042.
Actually I don't see anything wrong in coding. so it is puzzling me.

I hope this is clear. Thanks.

Regards, Magix

 

[Dave Peterson]

Maybe:

NameValue = range("AA" & x).Text

To show what's in the cell--if it's a number, .text will show the value as it's
formatted.

Or you could just check:

if iserror(range("aa" & x).value) then
'do nothing
else
namevalue = range("aa" & x).value
....

end if