VBA code break line error

[davidmg1982]

HI everyone, im requesting you help to understand the followin issue, im learning about VBA and most of my formulas are translated into VBA by using the recorder, but with this one:

=IF(A2="Dash Record"," -PO Title: "&CHAR(10)&B2&CHAR(10)&" -Item Description: "&CHAR(10)&U1&CHAR(10)&" -Cost Center total: "&CHAR(10)&"Cost Center: "&G2&" l Qty: "&TEXT(V2,"#,##0 ;")&" l Price: "&TEXT(W2,"$ #,##0.00 ;"),IF(G2=G1,U1&CHAR(10)&("PO Line: "&A2&" l Title: "&C2&" l Qty: "&TEXT(H2,"#,##0 ;")&" l Price: "&TEXT(I2,"$ #,##0.00 ;")&" l Cost Center: "&G2&" l GL: "&E2),("PO Line: "&A2&" l Title: "&C2&" l Qty: "&TEXT(H2,"#,##0 ;")&" l Price: "&TEXT(I2,"$ #,##0.00 ;")&" l Cost Center: "&G2&" l GL: "&E2)))

Translation
"=IF(RC[-20]=""Dash Record"","" -PO Title: ""&CHAR(10)&RC[-19]&CHAR(10)&"" -Item Description: ""&CHAR(10)&R[-1]C&CHAR(10)&"" -Cost Center total: ""&CHAR(10)&""Cost Center: ""&RC[-14]&"" l Qty: ""&TEXT(RC[1],""#,##0 ;"")&"" l Price: ""&TEXT(RC[2],""$ #,##0.00 ;""),IF(RC[-14]=R[-1]C[-14],R[-1]C&CHAR(10)&(""PO Line: ""&RC[-20]&"" l Title: ""&RC[-18]&"" l Qty: """& _
"TEXT(RC[-13],""#,##0 ;"")&"" l Price: ""&TEXT(RC[-12],""$ #,##0.00 ;"")&"" l Cost Center: ""&RC[-14]&"" l GL: ""&RC[-16]),(""PO Line: ""&RC[-20]&"" l Title: ""&RC[-18]&"" l Qty: ""&TEXT(RC[-13],""#,##0 ;"")&"" l Price: ""&TEXT(RC[-12],""$ #,##0.00 ;"")&"" l Cost Center: ""&RC[-14]&"" l GL: ""&RC[-16])))"

The recorder is creating a break line and its incomplete, i tried adding the missing part, but still turns yellow when runs, is there any other way to trasnslate formulas easily. I bold the part of the text that is missing.

Any advise will be very appreciated.

 

[JoeMo]

The VBA Application.ConvertFormula Method (see VBA Help) has a 255 character limit on the formula you are trying to convert. I don't use the macro recorder so not sure if this is what you are seeing, but your xlA1-style formula is well beyond the 255 character limit. Consider breaking the formula into a number of substrings, each under the limit. Then convert each and concatenate them to produce the converted formula.

See: https://docs.microsoft.com/en-us/office/vba/api/excel.application.convertformula