CODEWITHSUNDEEP
vbaexcelequation
Joe
Joe

Reputation: 395

isNumeric returning wrong state in Excel VBA

In the code below, I was attempting to do a calculation if the number in the specific cell was numeric, else return number from other cell. I think that my implementation is incorrect as I only get the else state populating if the first cell is not numeric and vise versa. Can you tell me how to fix this?

This is an example of the data:

enter image description here

The 6th entry should return 27 in the Meas-LO column.

Thanks

Here is the code

Sub ReturnMarginal()

    'UpdatebySUPERtoolsforExcel2016
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWks As Worksheet
    Dim InterSectRange As Range
    Dim lowLimCol As Integer
    Dim hiLimCol As Integer
    Dim measCol As Integer

    Application.ScreenUpdating = False
    Set xWb = ActiveWorkbook
    For Each xWks In xWb.Sheets
        xRow = 1
        With xWks
           FindString = "LowLimit"
           If Not xWks.Rows(1).Find(FindString) Is Nothing Then
               .Cells(xRow, 16) = "Meas-LO"
               .Cells(xRow, 17) = "Meas-Hi"
               .Cells(xRow, 18) = "Min Value"
               .Cells(xRow, 19) = "Marginal"
               LastRow = .UsedRange.Rows.Count
               lowLimCol = Application.WorksheetFunction.Match("LowLimit", xWks.Range("1:1"), 0)
               hiLimCol = Application.WorksheetFunction.Match("HighLimit", xWks.Range("1:1"), 0)
               measLimCol = Application.WorksheetFunction.Match("MeasValue", xWks.Range("1:1"), 0)
                If IsNumeric(Cells(2, lowLimCol).Address(False, False)) Then
           .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False)
                 Else
           .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False)
                 End If                   

               .Range("Q2:Q" & LastRow).Formula = "=" & Cells(2, hiLimCol).Address(False, False) & "-" & Cells(2, measLimCol).Address(False, False)

               .Range("R2").Formula = "=min(P2,Q2)"
               .Range("R2").AutoFill Destination:=.Range("R2:R" & LastRow)

               .Range("S2").Formula = "=IF(AND(R2>=-3, R2<=3), ""Marginal"", R2)"
               .Range("S2").AutoFill Destination:=.Range("S2:S" & LastRow)

           End If

        End With
        Application.ScreenUpdating = True 'turn it back on
    Next xWks

End Sub

Upvotes: 0

Views: 431

Answers (2)

jamheadart
jamheadart

Reputation: 5323

After understanding what you meant for the second part of your problem, I think the quickest fix for how your formula is set up is to fill the whole column with a formula.

This will be quicker than looping through each cell in code to check if it is a number. You can fill the whole range with a formula that does the check on the spreadsheet itself: e.g. =IF(ISNUMBER(C1),C1-D1,C1)

To get that in your code, I would replace the whole if isNumeric then...

Replace this section:

If IsNumeric(Cells(2, lowLimCol).Address(False, False)) Then
    .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False)
Else
    .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False)
End If

With this line:

.Range("P2:P" & lastRow).Formula = "=IF(ISNUMBER(" & .Cells(2, measLimCol).Value2 & ")," & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False) & "," & Cells(2, measLimCol).Address(False, False) & ")"

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152515

Cells(2, lowLimCol).Address(False, False) Returns a string address not the value. So it will never be numeric.

Change to:

.Cells(2, lowLimCol).Value2

Upvotes: 2

Related Questions