Problem with recording a macro with long formula - works in Excel but not in macro

I have a long formula in an Excel sheet and when I record a macro that adds the formula to a cell, it jumps to the next line after a certain number of characters and adds " &_ at the end of the line but 4 or 5 of the characters that were there in excel are now not there in VBA code after the macro has recorded. I added the missing characters and the formula compiles correctly in VBA but when I try to run it on the data in the spreadsheet, I get a runtime error on the formula.

This is similar to another problem that was reported on this post:

http://www.excelforum.com/excel-gene...ml#post4226175

Can anyone please provide feedback on why these characters disappear and on why the code compiles correctly in VBA but I get the runtime error when trying to run?

Here is code:

Sub Intl_MTs_by_region_by_month()
 '
 ' Intl_MTs_by_region_by_month Macro
 '

 '
 Workbooks.Open Filename:= _
 "S:\CRT-General Equipment Forecast Lists\2015 Forecast Information\Tracking Sheet\KPI Summary\Automated KPI report\MaterialTransfers - ETM export - before breakout.xls"
 Sheets("Details1").Select
 Sheets("Details1").Copy Before:=Workbooks( _
 "Material Transfers - Intl - before breakout.xlsm").Sheets(3)
 Sheets("Details1").Select
 Sheets("Details1").Name = "2015 by region"
 Rows("1:1").Select
 Selection.AutoFilter
 Sheets("2015 by region").Select
 Sheets("2015 by region").Copy Before:=Sheets(4)
 Sheets("2015 by region (2)").Select
 Sheets("2015 by region (2)").Name = "2015 by month"
 Sheets("2015 by region").Select
 Columns("E:E").Select
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("E1").Select
 Selection.NumberFormat = "General"
 ActiveCell.FormulaR1C1 = "'Region"
 Range("E2").Select
 ActiveCell.FormulaR1C1 = _
 "=IF(RC[-1]="""","""",IF(OR(RC[-1]=""ANGLU"",RC[-1]=""ANGSO"",RC[-1]=""BEN"",RC[-1]=""CAM"",RC[-1]=""CHAD"",RC[-1]=""CI"",RC[-1]=""CON"",RC[-1]=""EQG"",RC[-1]=""ETH"",RC[-1]=""GAB"",RC[-1]=""GABFZC"",RC[-1]=""GHA"",RC[-1]=""KEN"",RC[-1]=""LIB"",RC[-1]=""LIBTRI"",RC[-1]=""MAUR"",RC[-1]=""MORO"",RC[-1]=""MOZ"",RC[-1]=""MOZUK"",RC[-1]=""NAM"",RC[-1]=""NAMME"",RC[-1]=""N" & _
 "IG"",RC[-1]=""SAF"",RC[-1]=""SRL"",RC[-1]=""TANZ"",RC[-1]=""TOGO"",RC[-1]=""UGD""),""Africa"",IF(OR(RC[-1]=""ABD"",RC[-1]=""ABDOE"",RC[-1]=""ALG"",RC[-1]=""ASTRUS"",RC[-1]=""BRFIA"",RC[-1]=""CANARY"",RC[-1]=""DEN"",RC[-1]=""FRA"",RC[-1]=""GER"",RC[-1]=""GRL"",RC[-1]=""GRY"",RC[-1]=""GRYP"",RC[-1]=""HOL"",RC[-1]=""HOLOE"",RC[-1]=""ITA"",RC[-1]=""KAZ"",RC[-1]=""LDN"",RC[-1]=" & _
 """LOWE"",RC[-1]=""NOR"",RC[-1]=""RUS""),""Europe"",IF(OR(RC[-1]=""AUS"",RC[-1]=""AUSPER"",RC[-1]=""BAL"",RC[-1]=""BAN"",RC[-1]=""BRU"",RC[-1]=""CHI"",RC[-1]=""JAK"",RC[-1]=""JAP"",RC[-1]=""KOR"",RC[-1]=""MAL"",RC[-1]=""MALKE"",RC[-1]=""MALKL"",RC[-1]=""MALLA"",RC[-1]=""MALMY"",RC[-1]=""NZ"",RC[-1]=""PHI"",RC[-1]=""RUSFLS"",RC[-1]=""SAK"",RC[-1]=""SIN"",RC[-1]=""SINFLS"",RC[-1" & _
 "]=""THAIFLS"",RC[-1]=""VIET"",RC[-1]=""VTMFLS""),""Far East"",IF(OR(RC[-1]=""AZE"",RC[-1]=""DUB"",RC[-1]=""DUBLLC"",RC[-1]=""DUBFZC"",RC[-1]=""EGY"",RC[-1]=""ETIM"",RC[-1]=""IND"",RC[-1]=""IRQ"",RC[-1]=""IRQME"",RC[-1]=""ISR"",RC[-1]=""OMAN"",RC[-1]=""PAK"",RC[-1]=""QTR"",RC[-1]=""ROMA"",RC[-1]=""SAU"",RC[-1]=""SLK"",RC[-1]=""TUR"",RC[-1]=""TURK"",RC[-1]=""UKR"",RC[-1]=""YEM""" & _
 "),""Middle East"",IF(OR(RC[-1]=""ALV"",RC[-1]=""ALVPI"",RC[-1]=""ANCH"",RC[-1]=""AOT"",RC[-1]=""BOSC"",RC[-1]=""BRY"",RC[-1]=""BUR"",RC[-1]=""CALW"",RC[-1]=""CAR"",RC[-1]=""CC"",RC[-1]=""COGJ"",RC[-1]=""CWS"",RC[-1]=""EKC"",RC[-1]=""ELK"",RC[-1]=""FWS61"",RC[-1]=""GBP"",RC[-1]=""HMA"",RC[-1]=""HOB"",RC[-1]=""HOU"",RC[-1]=""HOUFCC"",RC[-1]=""HOUFTS"",RC[-1]=""HOUOSL"",RC[-1]" & _
 "RC[-1]=""KIL"",RC[-1]=""LBL"",RC[-1]=""LFT"",RC[-1]=""LFTCER"",RC[-1]=""LFTEIR"",RC[-1]=""LFTFI"",RC[-1]=""LFTHT"",RC[-1]=""LFTMFG"",RC[-1]=""LFTMMC"",RC[-1]=""LFTPC"",RC[-1]=""LFTSOG"",RC[-1]=""LNGV"",RC[-1]=""LRD"",RC[-1]=""LUL"",RC[-1]=""MAS"",RC[-1]=""MCA"",RC[-1]=""MNT"",RC[-1]=""ODA"",RC[-1]=""OKC"",RC[-1]=""OSL61"",RC[-1]=""POI"",RC[-1]=""PRUB"",RC[-1]=""PYN"",RC[-1]=""SGR""," & _
 "RC[-1]=""TCCAR"",RC[-1]=""TCCBD"",RC[-1]=""TCEDI"",RC[-1]=""TCELC"",RC[-1]=""TCLFT"",RC[-1]=""TCPLS"",RC[-1]=""UTHV"",RC[-1]=""WND"",RC[-1]=""WWR"",RC[-1]=""WYOC"",RC[-1]=""WYOCH"",RC[-1]=""WYOE"",RC[-1]=""WYOR""),""North America"",IF(OR(RC[-1]=""ARG"",RC[-1]=""BRAM"",RC[-1]=""COLBAR"",RC[-1]=""COLBO"",RC[-1]=""COLYOP"",RC[-1]=""ECU"",RC[-1]=""LIM"",RC[-1]=""MEX"",RC[-1]=" & _
 """TRI"",RC[-1]=""TRISRN"",RC[-1]=""VENA"",RC[-1]=""VENO"",RC[-1]=""VENBAR"",RC[-1]=""CANE"",RC[-1]=""CANES"",RC[-1]=""CANFN"",RC[-1]=""CANGP"",RC[-1]=""CANHA"",RC[-1]=""CANMD"",RC[-1]=""CANNF"",RC[-1]=""CANPP"",RC[-1]=""CANRD"",RC[-1]=""CANSS""),""Latin America & Canada"")))))))"
 Range("E2").Select
 Selection.AutoFill Destination:=Range("E2:E25000"), Type:=xlFillDefault
 Range("E2:E12919").Select
 ActiveWindow.ScrollRow = 12858
 ActiveWindow.ScrollRow = 12791
 ActiveWindow.ScrollRow = 11073
 ActiveWindow.ScrollRow = 9889
 ActiveWindow.ScrollRow = 2318
 ActiveWindow.ScrollRow = 1418
 ActiveWindow.ScrollRow = 1
 Columns("D:D").Select
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Sheets("2015 by month").Select
 Columns("D:D").Select
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("D1").Select
 Selection.NumberFormat = "General"
 ActiveCell.FormulaR1C1 = "'Month"
 Range("D2").Select
 ActiveCell.FormulaR1C1 = "=RC[-1]"
 Range("D2").Select
 Selection.AutoFill Destination:=Range("D2:D12919")
 Range("D2:D25000").Select
 Columns("D:D").Select
 Selection.NumberFormat = "mmmm"
 Range("C21").Select
 Sheets("2015 by region").Select
 Range("D1").Select
 Selection.NumberFormat = "General"
 ActiveCell.FormulaR1C1 = "'Month"
 Range("D2").Select
 ActiveCell.FormulaR1C1 = "=RC[-1]"
 Range("D2").Select
 Selection.AutoFill Destination:=Range("D2:D25000")
 Range("D2:D25000").Select
 Columns("D:D").Select
 Selection.NumberFormat = "mmmm"
 Range("D15").Select
 ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort.SortFields.add Key _
 :=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 With ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort.SortFields.add Key _
 :=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 With ActiveWorkbook.Worksheets("2015 by region").AutoFilter.Sort
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 Selection.Subtotal GroupBy:=6, Function:=xlCount, TotalList:=Array(6), _
 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
 Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
 Replace:=False, PageBreaks:=False, SummaryBelowData:=True
 ActiveSheet.Outline.ShowLevels RowLevels:=3
 Columns("C:C").ColumnWidth = 16.86
 Columns("C:C").ColumnWidth = 20
 Sheets("2015 by month").Select
 ActiveWorkbook.Worksheets("2015 by month").AutoFilter.Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("2015 by month").AutoFilter.Sort.SortFields.add Key _
 :=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 With ActiveWorkbook.Worksheets("2015 by month").AutoFilter.Sort
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
 ActiveSheet.Outline.ShowLevels RowLevels:=2
 Columns("C:C").ColumnWidth = 22.14
 Sheets("2015 by region").Select
 Range("C23").Select
 Application.WindowState = xlNormal
 Windows("Norway.xlsx").Activate
 Range("B1647").Select
 ActiveWindow.SmallScroll Down:=-273
 Range("E1362:E1364").Select
 Selection.Rows.Autofit
 Range("E1362").Select
 ActiveWorkbook.Save
 Range("C1368").Select
 Windows("Material Transfers - Intl - before breakout.xlsm").Activate
 End Sub

The active cell formula in red text is the line with the problem.


Thanks.

Thanks Norie,

If the formula compiled OK in the VBA editor, shouldn't it be OK how it is?

Do you know if the reason I am getting a runtime error (when I try to run the macro) is due to the length of the formula or is due to something else? If it is due to the length of the formula, I am not sure how I could shorten it...given that all of the if and or statements need to be there to return the correct "region" in the cell.

Thanks.