CODEWITHSUNDEEP
vbaexcel
pnwAnalyst
pnwAnalyst

Reputation: 27

InputBox value as the value_if_error for IFERROR INDEX/MATCH

I'm trying to use the InputBox result as the condition if the INDEX/MATCH doesn't return a value. Will be a four character string.

All results I found are for numbers or ranges. None 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 is a condensed version of the macro.

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

Upvotes: 1

Views: 285

Answers (1)

CLR
CLR

Reputation: 12279

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

Upvotes: 1

Related Questions