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?
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?
"if it's working don't mend it".
Do not solve
It works.
This underscore is a continuation sign, which tells VBA to treat next line not as "new line" but a continuation of a previous one.
Best Regards,
Kaper
There is missing information from the formula on the next line after the underscore.
Than, that is the problem (missing information).
But you don't add much information, to solve your problem.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
This is the formula I enterAnd this is the code it shows in the macroPlease Login or Register to view this content.
The formula I enter is complete but when recorded its not complete. For example this is wrongPlease Login or Register to view this content.
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.
Hi tonybeo2,
2 frank suggestions (both related to http://www.excelforum.com/forum-rule...rum-rules.html ):
- use code tags
- start your own thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks