CODEWITHSUNDEEP
vbaexcel
agustin
agustin

Reputation: 1351

VBA - Why IsNumeric is not evaluating correctly

I have following code in VBA to evaluate a variable lngPNumber to send the "correct" value to a WorksheetFunction.vLookup function:

Dim lngPNumber As Variant
lngPNumber = ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Value
        If IsNumeric(lngPNumber) = False Or CDbl(lngPNumber) <> Round(CDbl(lngPNumber)) Then
        lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)
        End If

lngPNumbercan be:

In the last both cases, I want to send the Cell Text and not the Cell Value where lngPNumberis obtained.

However, I get a Type Missmatch error if the value is a string like in the last example in the list. Any help?

Upvotes: 0

Views: 357

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

The If will try to resolve both sides of the Or regardless if the first is false or true, so CDbl(lngPNumber) will error if lngPNumber is text.

So split them into two.

Dim lngPNumber As Variant
lngPNumber = ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Value
        If Not IsNumeric(lngPNumber) Then
            lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)
        ElseIF CDbl(lngPNumber) <> Round(CDbl(lngPNumber)) Then
            lngPNumber = CStr(ActiveSheet.Cells(objInitialCell.Row, INT_ACCP_COL_PNUMBER).Text)     
        End If

Now the second will fire only if lngPNumber is a number.

Upvotes: 6

Related Questions