Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Single line of code with line separator
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Single line of code with line separator
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Single line of code with line separator
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," & _


"KarenF" wrote:

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Single line of code with line separator
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.

"KarenF" wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Single line of code with line separator
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,"""")))))))"

--


Gary


"KarenF" wrote in message
...
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Single line of code with line separator
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" wrote in message
...
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? No Name Excel Worksheet Functions 7 October 7th 09 11:10 AM
How show single line rather than multiple line with same desc in e nick New Users to Excel 1 July 1st 09 08:16 AM
Concatenate: Multi-Line Cell with 2 single line cells BEEJAY Excel Worksheet Functions 0 February 15th 07 08:53 PM
how do you execute single line of code? honestlylion Excel Discussion (Misc queries) 2 February 24th 06 03:35 PM
VBA Array Population with a Single Line of Code James B Excel Programming 7 February 6th 04 09:37 AM


All times are GMT +1. The time now is 02:14 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement