VBA Mulitple lines for one array

[smakatura]

I have a formula (with very long variable names). I replaced the names with VAR1, VAR2 and VAR3 in the sample below for readability. I have tried multiple ways but no able to get it to work

I want the formula to show on multiple line in the vba so that is easier to read.

I have tried multiple variants including

"Line1 _
Line2" there is a space before the _

"Line1 _
& Line2" there is a space before the _

but not working. I get a compilation error after the first row.

my actual code is (with shortened variable names)

ActiveCell.FormulaR1C1 = _
        "=IF(AND(LEN(VAR1)=1,LEN(VAR2)=1), _
         CONCATENATE(""0"",VAR2,""/0"",VAR1,""/"", _
         VAR3), _
         IF(AND(LEN(VAR1)=1,LEN(VAR2)=2), _
         CONCATENATE(VAR2,""/0"",VAR1,""/"", _
         VAR3), _
         IF(AND(LEN(VAR1)=2,LEN(VAR2)=1), _
         CONCATENATE(""0"",VAR2,""/"",VAR1,""/"", _
         VAR3),CONCATENATE(VAR2,""/"", _
         VAR3))))"

 

[DanteAmor]

You have to put quotation marks at the end and at the beginning of each line, in addition to using the & to chain the lines:

ActiveCell.FormulaR1C1 = _
         "=IF(AND(LEN(VAR_1)=1,LEN(VAR_2)=1)," & _
         "CONCATENATE(""0"",VAR_2,""/0"",VAR_1,""/"",VAR_3)," & _
         "IF(AND(LEN(VAR_1)=1,LEN(VAR_2)=2)," & _
         "CONCATENATE(VAR_2,""/0"",VAR_1,""/"", VAR_3)," & _
         "IF(AND(LEN(VAR_1)=2,LEN(VAR_2)=1)," & _
         "CONCATENATE(""0"",VAR_2,""/"",VAR_1,""/"", VAR_3)," & _
         "CONCATENATE(VAR_2,""/"",VAR_3))))"

another option

ActiveCell.FormulaR1C1 = _
         "=IF(AND(LEN(VAR_1)=1,LEN(VAR_2)=1)," & _
         "CONCATENATE(""0"",VAR_2,""/0"",VAR_1,""/""," & _
         "VAR_3)," & _
         "IF(AND(LEN(VAR_1)=1,LEN(VAR_2)=2)," & _
         "CONCATENATE(VAR_2,""/0"",VAR_1,""/""," & _
         "VAR_3)," & _
         "IF(AND(LEN(VAR_1)=2,LEN(VAR_2)=1)," & _
         "CONCATENATE(""0"",VAR_2,""/"",VAR_1,""/""," & _
         "VAR_3)," & _
         "CONCATENATE(VAR_2,""/""," & _
         "VAR_3))))"

 

[offthelip]

Excel will read your your script as text from the first double quotes up until the second double quotes CONCATENATE(""0 at this point is sees the first string ending and a second string starting but it doesn't know what to do with it.
Normally when trying to put a double quote into the equation I use ascii character chr(34).
This example will show you what I mean:

ActiveCell.FormulaR1C1 = _
        "=IF(R1C1," & Chr(34) & "test" & Chr(34) & "," & Chr(34) & "DD" & Chr(34) & ")"

Another thing which i find very helpful when trying to solve the problem of writing equations into cells with VBA is to start writing into the cell as TEXT by ommitting the equals sign eg

ActiveCell = _
        "IF(AND(LEN(VAR1)=1,LEN(VAR2)=1), _
         CONCATENATE(""0"",VAR2,""/0"",VAR1,""/"", _
         VAR3), _
         IF(AND(LEN(VAR1)=1,LEN(VAR2)=2), _
         CONCATENATE(VAR2,""/0"",VAR1,""/"", _
         VAR3), _
         IF(AND(LEN(VAR1)=2,LEN(VAR2)=1), _
         CONCATENATE(""0"",VAR2,""/"",VAR1,""/"", _
         VAR3),CONCATENATE(VAR2,""/"", _
         VAR3))))"

Then you can manually edit the cell and put the equals sign in and excel will tell you where the problem with the equation is.

 

[smakatura]

Thank you that worked

I had tried the " & _ " Previously at the end of the lines but had no put the " at the beginning an end of the formula part of the line

 

[DanteAmor]

I'm glad to help you