isNumeric returning wrong state in Excel VBA

Question

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:

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

Scott Craner · Answer 1 · 2017-06-24T17:01:01.183

2

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

edited Jun 24 '17 at 17:01

answered Jun 24 '17 at 15:33

Scott Craner

148,073
10
49
81

1

I'd also say slightly better practice to stick the `.` in front of `cells` : `.cells(2, lowLimCol).value2` ? Am I right there? Just to direct it to the `With` worksheet... low chance of error but maybe user clicking on random things during a run could cause it to reference the cell on another sheet? I've had issues with that before where users running one of my lengthy codes are clicking away in Outlook or Word or another Excel chart and the final result was a table full of 0000 – jamheadart Jun 24 '17 at 15:59
@Scott Craner, Thanks for your solution. I did put the command `If IsNumeric(.Cells(2, lowLimCol).Value2) Then`, but I still only get the "MeasValue" if the first entry is not a numeric. Am I doing something with the second line, `.Range("P2:P" & LastRow).Formula =...`, that is messing me up? Thanks. – Joe Jun 24 '17 at 18:11
@jamheadart, do I now have to put the formula block in a `while loop` now as the entries may either be a numeric or not? – Joe Jun 24 '17 at 18:18
1

There's a problem with your if... else... end if logic. You've got `If isnumeric then (A) else (B) End if` but then the next line after that is just (A) again... this means that (A) is gonna happen no matter what the outcome of your isnumeric check? – jamheadart Jun 24 '17 at 18:32
@jamheadart, sorry...I deleted the line but I get the same outcome. I think that column is just getting populated with the condition depending only on the first condition. It is not dynamic. That is, some of the cells may be numeric and others not, however, once the first cell is set, it determines the outcome for the whole column. Thanks. – Joe Jun 24 '17 at 22:03
Ohhh, I see what you mean now, check out an answer I provide below: – jamheadart Jun 24 '17 at 22:28

score 1 · Accepted Answer · answered Jun 24 '17 at 22:38

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) & ")"

Thanks for your solution. I tried your suggestion but still do not get the correct output. I have added an image of the data layout. Thanks. — Joe, Jun 24 '17 at 23:40

isNumeric returning wrong state in Excel VBA

2 Answers2

Linked