IsNA and Match Function Error in VBA

[shippj]

I am trying to test a list to verify that a string (Name) exists before I move on in the program. I was trying to use an IsNa function combined with a Match function to generate a True or False answer.

The code works if the code generates a variable that is False and fails if the value is True? I am lost as to why it works in one case and not the other.

Thanks ahead for your help. Test code is listed below.

John

Sub TestProcedure3()
Dim varAnswer As String
Dim varSSDNo As String

'Input a variable String.
varSSDNo = InputBox("Enter SSD Number.", "Enter SSD Number to Delete")
MsgBox "varSSDNo is: " & varSSDNo

' Test to see if the string is in the list range "TableDataFieldNames"
' I expect to get a False if it is in the list and a True if it is not in the list.

varAnswer = Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(varSSDNo, Range("TableDataFieldNames"), 0))

'The above line of code works if the answer is False and bombs if the answer is True.
'Question is why??? and how do I get around it.

' I am testing for inclusion in a list before I continue. I can easily do this with formula in cells.
MsgBox "varAnswer is " & varAnswer

End Sub

 

[VoG]

Try

If IsNumeric(Application.Match(varSSDNo, Range("TableDataFieldNames"), 0)) Then
    varAnswer = "True"
Else
    varAnswer = "False"
End If

 

[DonkeyOte]

or even:

varAnswer = CStr(IsNumeric(Application.Match(varSSDNo, Range("TableDataFieldNames"), 0)))

 

[VoG]

or even:

varAnswer = CStr(IsNumeric(Application.Match(varSSDNo, Range("TableDataFieldNames"), 0)))

Much too clever

 

[shippj]

or even:

varAnswer = CStr(IsNumeric(Application.Match(varSSDNo, Range("TableDataFieldNames"), 0)))

The code works perfectly. I do not understand how the CStr works (tried looking it up) but it does exactly what I wanted.

Thanks,

john

 

[shippj]

Try

If IsNumeric(Application.Match(varSSDNo, Range("TableDataFieldNames"), 0)) Then
    varAnswer = "True"
Else
    varAnswer = "False"
End If

This works fine. Donkeote had a one liner that was need but I need the if statement anyway so your solution is shorter for me, just put the program in and ignore the answer. Thanks,

John