VBA Selection.Formula returning "False" instead of "N/A"

Question

I am using VBA to run a set of data against five "rule" columns stored in another sheet in the workbook. Put simply, I seem to have code which works, but the VBA use of Selection.Formula = returns "False" when an cell formula would return #N/A or #VALUE. It's critical that I get the error values because it tells the user something different than "False". False should mean that column C (see picture of calculation tab below) doesn't pass the rule. The error values mean that either column B is not found with VLookup in the Rules column or the rule was written incorrectly.

Here's what I have so far:

    Sub Build_Formulas_v2()

    Application.Calculation = xlManual
    Range("a2", Range("a65536").End(xlUp)).Offset(0, 6).Select
    Selection.Value = _
    Evaluate("(""=""&SUBSTITUTE(VLOOKUP(B2,'Logic Statements'!A:E,4,FALSE),""ZZZ"",""c""&ROW()))")

    End Sub

Any help would be tremendously appreciated - my VBA knowledge is still growing and is too basic to understand what I'm up against.

@SiddharthRout +1 I thought he was examining/comparing to the error value, not trying to write it. thx! — David Zemens

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-10-15T17:28:03

I believe you are using Excel 2003. You should never hard code values like A65536. You can get undesirable results in xl2007+ as they have 1048576 rows.

Is this what you are trying?

Sub Build_Formulas_v2()
    Dim lRow As Long
    Dim ws As Worksheet

    Application.Calculation = xlManual

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("A2:A" & lRow).Offset(0, 6).Formula = _
        "=SUBSTITUTE(VLOOKUP(B2,'Logic Statements'!A:E,4,FALSE),""ZZZ"",""c""&ROW())"

        '~~> Uncomment the below in case you want to replace formulas with values
        '.Range("A2:A" & lRow).Offset(0, 6).Value = .Range("A2:A" & lRow).Offset(0, 6).Value
    End With
End Sub

Or if you do not want to use .Offset, then you can directly address Column G

Sub Build_Formulas_v2()
    Dim lRow As Long
    Dim ws As Worksheet

    Application.Calculation = xlManual

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("G2:G" & lRow).Formula = _
        "=SUBSTITUTE(VLOOKUP(B2,'Logic Statements'!A:E,4,FALSE),""ZZZ"",""C""&ROW())"

        '~~> Uncomment the below in case you want to replace formulas with values
        '.Range("A2:A" & lRow).Offset(0, 6).Value = _
        .Range("A2:A" & lRow).Offset(0, 6).Value
    End With
End Sub

VBA Selection.Formula returning "False" instead of "N/A"

1 Answers