Recorded macro continues to return error.

[janugent]

Hello-

I am working on an automation project within the plant to display on multiple monitors a list of production orders. I have recorded a macro that will insert a column and define the Location of a Production Order based upon the Work Center. There are no issues whiles recording the macro, but when I go to use it again, I am getting an error. It has to do with the ActiveCell.FormulaR1C1, I believe. I have listed it below....any help would be greatly appreciated.

Sub TRIAL1()
'
' TRIAL1 Macro
'

'
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "LOCATION"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]=""1A"",""MACH SHOP"",IF(RC[1]=""1B"",""MACH SHOP"",IF(RC[1]=""1C"",""MACH SHOP"",IF(RC[1]=""1D"",""MACH SHOP"",IF(RC[1]=""1E"",""MACH SHOP"",IF(RC[1]=""1F"",""MACH SHOP"",IF(RC[1]=""1G"",""MACH SHOP"",IF(RC[1]=""1I"",""MACH SHOP"",IF(RC[1]=""1L"",""MACH SHOP"",IF(RC[1]=""1N"",""MACH SHOP"",IF(RC[1]=""1P"",""MACH SHOP"",IF(RC[1]=""1R"",""MACH SHOP"",IF(RC[1" & _
",""MACH SHOP"",IF(2=""1T"",""MACH SHOP"",IF(RC[1]=""1V"",""MACH SHOP"",IF(RC[1]=""2D"",""FABRICATION"",IF(RC[1]=""2E"",""FABRICATION"",IF(RC[1]=""2F"",""FABRICATION"",IF(RC[1]=""2G"",""FABRICATION"",IF(RC[1]=""2H"",""FABRICATION"",IF(RC[1]=""2J"",""FABRICATION"",IF(RC[1]=""2K"",""FABRICATION"",IF(RC[1]=""2L"",""FABRICATION"",IF(RC[1]=""3E"",""FABRICATION"",IF(RC[1]=" & _
"FABRICATION"",IF(RC[1]=""3G"",""FABRICATION"",IF(RC[1]=""3I"",""FABRICATION"",IF(RC[1]=""1M"",""CUTTING"",IF(RC[1]=""3A"",""CUTTING"",IF(RC[1]=""3B"",""CUTTING"",IF(RC[1]=""3C"",""CUTTING"",IF(RC[1]=""3H"",""CUTTING"",IF(RC[1]=""3K"",""CUTTING"",IF(RC[1]=""4C"",""ASSEMBLY"",IF(RC[1]=""4D"",""ASSEMBLY"",IF(RC[1]=""4E"",""ASSEMBLY"",IF(RC[1]=""4F"",""ASSEMBLY"",IF(RC[" & _
",""ASSEMBLY"",IF(RC[1]=""4I"",""ASSEMBLY"",IF(RC[1]=""4J"",""ASSEMBLY"",IF(RC[1]=""4X"",""ASSEMBLY"",IF(RC[1]=""4B"",""REPAIR ASSY"",IF(RC[1]=""4R"",""REPAIR ASSY"",IF(RC[1]=""4S"",""REPAIR ASSY"",IF(RC[1]=""4T"",""REPAIR ASSY"",IF(RC[1]=""4U"",""REPAIR ASSY"",IF(RC[1]=""4V"",""REPAIR ASSY"",IF(RC[1]=""4W"",""REPAIR ASSY"",IF(RC[1]=""4K"",""SHIPPING"",IF(RC[1]=""ENG" & _
"REWORK"",IF(RC[1]=""IC"",""INVENTORY CTRL"",IF(RC[1]=""OP"",""OUTSIDE PROD"",IF(RC[1]=""QC"",""QUALITY CTRL"",IF(RC[1]=""RE"",""REWORK"","" ""))))))))))))))))))))))))))))))))))))))))))))))))))))))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D1258")
Range("D2:D1258").Select
End Sub

 

[Joe4]

Welcome to the Board!

How many nested IF statements do you have? I thought 7 was the limit.
You may be better served by created a lookup table and using a VLOOKUP table than nesting that many levels of IF.
See: MS Excel: How to use the VLOOKUP Function (WS)

 

[janugent]

There are several.

It could be changed to use a range: "1A, 1B, 1C, 1D","MACH SHOP", but I would still have 11 statements.

I need it to insert a column, identify it, and input a Location. If I used a VLookUp, I would have to create a table, and I would just run that every time.

 

[janugent]

just want to make sure that I explained what I needed. I am downloading a report from within our system. It defines the production orders by work centers (1a,1b,1c,1d). These work centers are broken into locations (machine shop, fab, etc.). I am needing to run a macro that will insert a new column to the left of the work centers and specify which location these are for.

 

[Joe4]

If you want everything contained within a macro, I would recommend using a CASE statement instead of a messy, nested IF statement.
See: MS Excel: How to use the CASE Statement (VBA)

 

[janugent]

Here is a detailed layout of what I am needing help with. The report comes from our system. I am needing to insert a column and place the work centers into different categories (1a-machine shop, 2d-fabrication, 4c-assembly, 4b-repair assy, etc.).

date order description work center
1/2/2016 123 roll 1a
1/3/2016 456 machine 2d
1/4/2016 556 hall 4c
1/5/2016 789 main 4b

 

[Joe4]

OK. Here is code that should do what you want. Just continue on and add more "Case" statements for all your other options.

Sub TRIAL1()
' TRIAL1 Macro

    Dim cell As Range
    Dim category As String

    Application.ScreenUpdating = False

'   Insert blank column in column D
    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'   Put header on column D
    Range("D1") = "LOCATION"
    
'   Loop through all entries in column E, placing the appropriate value in column D
    For Each cell In Range("E2:E" & Range("E2").End(xlDown).Row)
        category = ""
        Select Case cell
            Case "1a"
                category = "machine shop"
            Case "2d"
                category = "fabrication"
            Case "4c"
                category = "assembly"
            Case "4b"
                category = "repair"
'           enter the rest of your options below
            Case Else
                category = "unknown"
        End Select
        cell.Offset(0, -1) = category
    Next cell
    
    MsgBox "Process complete!"
    
    Application.ScreenUpdating = True
            
End Sub