Multiple IF's causing Run-time error 1004

July 31, 2024

I get the run-time error. I added a couple IF’s to the original formula. I believe the error is caused by multiple duplicate results (see the code below) like RTP and PNYP. If this is the case, how can I get the desired results? This is for code farther down the line to do CountIfs and SumIfs the result appears in a worksheet column.

With Sheet12 'Sheet12 = Raw Data - DS
        .Columns("C:D").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("G2:G" & .Cells(.Rows.count, "F").End(xlUp).Row).FormulaR1C1 = _
                       "=IF(RC[-1]=""Crossdock"",""CD"",IF(RC[-1]=""ReadyToPick"",""RTP"",IF(RC[-1]=""ReadyToPickUnconstrained"",""RTP"",IF(RC[-1]=""PickingPicked"",""PP"",IF(RC[-1]=""PickingPickedAtDestination"",""PP"",IF(RC[-1]=""PickingNotYetPickedPrioritized"",""PNYP"",IF(RC[-1]=""PickingNotYetPickedNotPrioritized"",""PNYP"",IF(RC[-1]=""Loaded"",""LD"","""")))))"
    End With

>Solution :

Ideally this lookup would be on a list stored in a worksheet, but you can do it using an array directly in the formula:

=IFERROR(VLOOKUP(RC[-1],
    {"Crossdock","CD";
     "ReadyToPick","RTP";
     "ReadyToPickUnconstrained","RTP";
     "PickingPicked","PP"},
    2,FALSE),"")

(add the rest of your terms)