ISNUMBER returning #VALUE! error in formula with VBA

Question

This question is building from the solution found here. I wanted to be able to check if the "LowLimit" cell is a number. If it is then carry out equation, else return value from "MeasValue" column. Here is an example of the data set with my current outcome:

As you can see, the 6th data entry calculation gives the wrong calculation. The number LowLimit value of 22 seems to be hard coded in the formula. Can you help me fix this? Thanks.

Here is the code that I have so far:

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).Value2) 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("P2:P" & lastRow).Formula = "=IF(ISNUMBER(" & .Cells(2, lowLimCol).Value & ")," & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False) & "," & Cells(2, measLimCol).Address(False, False) & ")"

        .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

To avoid hard-coded value you can try `.Address` instead of `.Value`. — A.S.H, Jun 25 '17 at 12:55
@AndreTerra - guess OP is using VBA because code presented implies `LowLimit`, `HighLimit` and `MeasValue` will not always be in columns `H`, `I` and `J`. — Robin Mackenzie, Jun 25 '17 at 15:50
@AndreTerra, I am using VBA because I have many Excel files with data that has the positions of the LowLimit, HighLimit and MeasValue that changes per sheet. Also, by having this coded up in VBA, I can easily distribute to my colleagues. What option were you thinking of? Thanks. — Joe, Jun 25 '17 at 17:08
You can use `MATCH` to find the position of those cells. Knowing that position you can use `INDEX` to pull the right cells (see e.g. https://www.deskbright.com/excel/using-index-match/) or use `OFFSET` to construct a range with more than 1 cell (see e.g. http://www.mbaexcel.com/excel/how-to-use-offset-match-match/). Even if you adamantly insist on VBA, then why not create a UDF? — airstrike, Jun 25 '17 at 17:12

Robin Mackenzie · Accepted Answer · 2017-06-25T15:49:10.047

I think the main improvement you can make here is to get the column letters for LowLimit, HighLimit and MeasValue once you establish where they are in row 1. Then you can refer to those column letters when you set the .Formula properties.

There is a helpful post on converting column numbers to letters here.

Also, you don't need to auto-fill columns R and S - you can populate in the same way you are doing for columns P and Q.

I updated your code a little - hope it helps:

Option Explicit

Sub ReturnMarginal()

    Dim ws As Worksheet
    Dim lngLowLimCol As Long, strLowLimCol As String
    Dim lngHiLimCol As Long, strHiLimCol As String
    Dim lngMeasCol As Long, strMeasCol As String
    Dim lngLastRow As Long
    Dim wsf As WorksheetFunction

    ' get worksheetfunction references
    Set wsf = Application.WorksheetFunction

    ' iterate worksheets
    For Each ws In ThisWorkbook.Worksheets

        ' validate LowLimit label is on sheet
        If ws.Rows(1).Find("LowLimit") Is Nothing Then Exit Sub

        ' get location of input data columns and number of rows
        lngLowLimCol = wsf.Match("LowLimit", ws.Rows(1), 0)
        lngHiLimCol = wsf.Match("HighLimit", ws.Rows(1), 0)
        lngMeasCol = wsf.Match("MeasValue", ws.Rows(1), 0)
        lngLastRow = ws.Cells(1, lngLowLimCol).End(xlDown).Row

        ' get column letters for input data columns
        strLowLimCol = Split(ws.Cells(1, lngLowLimCol).Address(True, False), "$")(0)
        strHiLimCol = Split(ws.Cells(1, lngHiLimCol).Address(True, False), "$")(0)
        strMeasCol = Split(ws.Cells(1, lngMeasCol).Address(True, False), "$")(0)

        ' output headers
        ws.Range("P1") = "Meas-LO"
        ws.Range("Q1") = "Meas-Hi"
        ws.Range("R1") = "Min Value"
        ws.Range("S1") = "Marginal"

        ' assign formulas to outputs
        ' Meas-LO
        With ws.Range("P2:P" & lngLastRow)
            .Formula = "=IF(ISNUMBER(" & strLowLimCol & "2)," & _
                strMeasCol & "2-" & strLowLimCol & "2," & _
                strMeasCol & "2)"
        End With

        ' Meas-Hi
        With ws.Range("Q2:Q" & lngLastRow)
            .Formula = "=" & strHiLimCol & "2-" & strMeasCol & "2"
        End With

        ' Min Value
        With ws.Range("R2:R" & lngLastRow)
            .Formula = "=MIN(P2,Q2)"
        End With

        ' Marginal
        With ws.Range("S2:S" & lngLastRow)
            .Formula = "=IF(AND(R2>=-3,R2<=3),""Marginal"",R2)"
        End With

    Next 'ws

End Sub

Output:

thanks for your solution. I just discovered that if on one sheet that "LowLimit" does not exist, the calculations are not done for the next worksheet that does contain all the headers. That is, as soon as the LowLimit" header is not found, no more calculations are carried out. Is there a way to fix this? Thanks! — Joe, Jun 26 '17 at 15:23
See this thread - https://stackoverflow.com/questions/20681306/skip-to-next-iteration-in-loop-vba - I would go for the answer provided by BH. — Robin Mackenzie, Jun 26 '17 at 23:29

ISNUMBER returning #VALUE! error in formula with VBA

1 Answers1

Linked