Error 2042

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 ?

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" <[email protected]> wrote in message news:[email protected]...
> 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 ?
>
>

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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:umpb%[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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 ?
> >
> >
>
>

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

magix wrote:
>
> 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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:umpb%[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > > 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 ?
> > >
> > >
> >
> >

--

Dave Peterson