Return ABS value from formula using Excel VBA

Question

In the code given below, that was orchestrated by Robin Mackenzie, I am now trying to get the absolute value from the equation. I tried using the ABS() command:

.Formula = "=IF(ISNUMBER(" & strLowLimCol & "2)," & _ strMeasCol & "ABS(2-" & strLowLimCol & "2," & _ strMeasCol & "2))"

I do not think that I am executing the command correctly. Can you help me get the abosulte value from the equation?

Here is the code:

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

This is still a terrible use of Excel and VBA – airstrike Jun 26 '17 at 05:05 — airstrike, Jun 26 '17 at 05:05

YowE3K · Accepted Answer · 2017-06-26T03:41:45.903

The easiest way is to put the Abs around the whole formula:

.Formula = "=ABS(IF(ISNUMBER(" & strLowLimCol & "2)," & _
            strMeasCol & "2-" & strLowLimCol & "2," & _
            strMeasCol & "2))"

But you could put it around each component if you wanted to:

.Formula = "=IF(ISNUMBER(" & strLowLimCol & "2),ABS(" & _
            strMeasCol & "2-" & strLowLimCol & "2),ABS(" & _
            strMeasCol & "2))"

The thing you have to remember is that strMeasCol & "2-" & strLowLimCol & "2 is creating a formula using two addresses, such as G2-K2 - so you can't start the ABS part way through that (i.e. you were putting the ABS( between the G and the 2 in my sample address, giving something like GABS(2-K2).

Return ABS value from formula using Excel VBA

1 Answers1