InputBox value as the value_if_error for IFERROR I

Question

Still new to VBA. I'm trying to use the InputBox result as the condition if the INDEX/MATCH doesn't return a value. Will be a 4 character string. All of the results that I've been finding are for numbers or ranges, and none of them were passing values as the error condition.

I feel like I'm 90% of the way there because inpMfrDiv displays the user input in the formula bar when I run the macro, but in the worksheet it displays #NAME? ERROR. inpMfrCode doesn't work at all and just puts the formula in the cells.

Here's a condensed version of the macro. Any help would be greatly appreciated. Thanks!

Sub PriceFileThings5()
    Dim wb As Workbook
    Dim lastRow As Long
    Dim rngDynamicO, rngDynamicP, rngDynamicQ, rngDynamicR, rngDynamicS, rngDynamicT As Range
    Dim inpMfrCode As String
    Dim inpMfrDiv As String

    lastRow = Sheets("Contract").Range("N1").End(xlDown).Row
    Set wb = ActiveWorkbook
    Set rngDynamicS = wb.Sheets("Contract").Range("S2:S" & lastRow)
    Set rngDynamicT = wb.Sheets("Contract").Range("T2:T" & lastRow)

    Application.DisplayAlerts = False
        Call OptimizeCode_Begin

            wb.Names.Add Name:="rngReturnR", _
                RefersTo:="=ItemMaster_Matches!$D$2:INDEX(ItemMaster_Matches!$D:$D, COUNTA(ItemMaster_Matches!$D:$D))"
            wb.Names.Add Name:="rngReturnS", _
                RefersTo:="=ItemMaster_Matches!$E$2:INDEX(ItemMaster_Matches!$E:$E, COUNTA(ItemMaster_Matches!$E:$E))"
            wb.Names.Add Name:="rngReturnT", _
                RefersTo:="=ItemMaster_Matches!$F$2:INDEX(ItemMaster_Matches!$F:$F, COUNTA(ItemMaster_Matches!$F:$F))"

    'NEED USER INPUT BOX FOR THESE RANGES
        '*************************

        With rngDynamicS
            inpMfrCode = InputBox("Please enter the 4 character Manufacturer Code")
            .Formula = "=IFERROR(INDEX(rngReturnS, MATCH(Contract!$A2, rngLookUp, FALSE)), " & inpMfrCode & ")"
            lastRow = Range("N1:N" & Range("N1").End(xlDown).Row).Rows.Count
            Range("S2" & ":S" & lastRow).FillDown
            'Application.Calculate
        End With

        With rngDynamicT
            inpMfrDiv = InputBox("Please enter the 4 character Manufacturer Division")
            .Formula = "=IFERROR(INDEX(rngReturnT, MATCH(Contract!$A2, rngLookUp, FALSE)), " & inpMfrDiv & ")"
            lastRow = Range("N1:N" & Range("N1").End(xlDown).Row).Rows.Count
            Range("T2" & ":T" & lastRow).FillDown
            'Application.Calculate
        End With
        '*************************


            ActiveSheet.Calculate
        Call OptimizeCode_End
    Application.DisplayAlerts = True
End Sub

Answer 1

You're putting the string inpMfrCode into the formula, without wrapping it in quotes.

That is to say, the formula it's creating looks like:

=IFERROR(INDEX(rngReturnS, MATCH(Contract!$A2, rngLookUp, FALSE)), ABCD)

When you actually want it to write:

=IFERROR(INDEX(rngReturnS, MATCH(Contract!$A2, rngLookUp, FALSE)), "ABCD")

Try swapping out your .Formula lines to:

.Formula = "=IFERROR(INDEX(rngReturnS, MATCH(Contract!$A2, rngLookUp, FALSE)), """ & inpMfrCode & """)"

and

.Formula = "=IFERROR(INDEX(rngReturnT, MATCH(Contract!$A2, rngLookUp, FALSE)), """ & inpMfrDiv & """)"