Formula cut off with underscore

I am trying to record a macro with a very long formula but after I'm done recording, the formula in the macro is cut off with a underscore "_" and it jumps to a new line with missing data from the formula.

Can someone tell me how to solve this?

There is missing information from the formula on the next line after the underscore.

This is the formula I enter

Please Login or Register  to view this content.

And this is the code it shows in the macro

Please Login or Register  to view this content.

The formula I enter is complete but when recorded its not complete. For example this is wrong

Please Login or Register  to view this content.

Anyone see the problem?

I am having this same problem. 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. Can anyone help with this?

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 is the line with the problem.


Thanks.