Single line of code with line separator

K

KarenF

Hello All -

I have an IF stmt that's fairly long, and it works.

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""abc"",1.5,
IF(OR(RC[-1]={""xx"",""xx""}),4.0,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3.5,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3, IF(OR(RC[-1]={""A6"",""AB"",""AV"",""ZU"",""xx""}),2.5,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),2, IF(OR(RC[-1]={""A1"",""A2"",""xx""}),1.5,"""")))))))"

However, if I try to break it into segments for useability, I get an
"Expected: end of statement" or "Syntax Error."

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""abc"",1.5, _
IF(OR(RC[-1]={""xx"",""xx""}),4.0, _
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3.5,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3, _
IF(OR(RC[-1]={""A6"",""AB"",""AV"",""ZU"",""xx""}),2.5, _
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),2, _
IF(OR(RC[-1]={""A1"",""A2"",""xx""}),1.5,"""")))))))"

The ONLY difference is the multiple lines. What is the magic to breaking up
a line when I want more than 1 row?

KarenF
 
J

JLGWhiz

You would have to concatenate the breaks. Here is a an example for the first
break:

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""abc"",1.5," & _
&"IF(OR(RC[-1]={""xx"",""xx""}),4.0," & _
 
J

JLGWhiz

Unfortunately, VBA sees the Formula all as one statement, so when you split a
single statement, You have to enclose the ending part in dbl quotes, insert a
concatenate symbol then space and the attenuation mark, and reverese the
process on the beginning of the next line.
 
G

Gary Keramidas

this will probably wrap, but just look for the _ and that's the end of each
line:

ActiveCell.FormulaR1C1 = _
"IF(OR(RC[-1]={""xx"",""xx""}),4.0," & _
"IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3.5,"
& _
"IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3,"
& _
"IF(OR(RC[-1]={""A6"",""AB"",""AV"",""ZU"",""xx""}),2.5," & _
"IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),2,"
& _
"IF(OR(RC[-1]={""A1"",""A2"",""xx""}),1.5,"""")))))))"
 
R

Rick Rothstein \(MVP - VB\)

Everything after this...

ActiveCell.FormulaR1C1 =

in your statement is a very long String value... you cannot do line
continuations in the middle of a String. For example, if you had this
line...

FakeLongLine = "This is supposed to be a long line"

you could NOT do this...

FakeLongLine = "This is supposed _
to be a long line"

which is what you did several times with your own long String value.
Instead, to do a line continuation on my example above, you would have to
form substrings, concatenate them and do the line continuation at the point
of concatenation....

FakeLongLine = "This is supposed" & _
"to be a long line"

Rick
 
K

KarenF

IT WORKS!

I have other concatenated rows with just the underscore at the end. They
work but they were created by recording the macro, not by me, so I guess
that's why I got so frustrated/confused. Thanks for this very simple
solution.

KarenF

Rick Rothstein (MVP - VB) said:
Everything after this...

ActiveCell.FormulaR1C1 =

in your statement is a very long String value... you cannot do line
continuations in the middle of a String. For example, if you had this
line...

FakeLongLine = "This is supposed to be a long line"

you could NOT do this...

FakeLongLine = "This is supposed _
to be a long line"

which is what you did several times with your own long String value.
Instead, to do a line continuation on my example above, you would have to
form substrings, concatenate them and do the line continuation at the point
of concatenation....

FakeLongLine = "This is supposed" & _
"to be a long line"

Rick


KarenF said:
Hello All -

I have an IF stmt that's fairly long, and it works.

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""abc"",1.5,
IF(OR(RC[-1]={""xx"",""xx""}),4.0,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3.5,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3,
IF(OR(RC[-1]={""A6"",""AB"",""AV"",""ZU"",""xx""}),2.5,
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),2,
IF(OR(RC[-1]={""A1"",""A2"",""xx""}),1.5,"""")))))))"

However, if I try to break it into segments for useability, I get an
"Expected: end of statement" or "Syntax Error."

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""abc"",1.5, _
IF(OR(RC[-1]={""xx"",""xx""}),4.0, _
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3.5,
_
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),3,
_
IF(OR(RC[-1]={""A6"",""AB"",""AV"",""ZU"",""xx""}),2.5, _
IF(OR(RC[-1]={""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx"",""xx""}),2,
_
IF(OR(RC[-1]={""A1"",""A2"",""xx""}),1.5,"""")))))))"

The ONLY difference is the multiple lines. What is the magic to breaking
up
a line when I want more than 1 row?

KarenF
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Help 5
Data samples 3
Problem with line of code / syntax 4
Creating Pivot Table with Macro using Multiple Sheets 0
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Help with Error 4
Help ... what wrong with this code 1
How to determine the logic of a complex task 0

Top