Wrap text in VBA
 

Hi all. I have a long formula in VBA:

ActiveCell.Formula = "=IF(ISBLANK(B" & cellrow & "),"""",IF(D" & cellrow &
"=""Yes"",LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),RIGHT(B" & cellrow &
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow &
")))),LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),""."",RIGHT(B" & cellrow &
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow & "))))))"


When I try to fo the "space underscore" to wrap the code down to the next
line, I get compile errors. Can I not use the _ to wrap text within a
formula?

Wrap text in VBA
 

Yes, but you can't break string integrity.

Instead of

s = "abcd _
efgh"

you have to do

s= "abcd" _
& "efgh"

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. I have a long formula in VBA:

ActiveCell.Formula = "=IF(ISBLANK(B" & cellrow & "),"""",IF(D" & cellrow
&
"=""Yes"",LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),RIGHT(B" & cellrow &
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow &
")))),LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),""."",RIGHT(B" & cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow & "))))))"


When I try to fo the "space underscore" to wrap the code down to the next
line, I get compile errors. Can I not use the _ to wrap text within a
formula?

Wrap text in VBA
 

Thanks Tom.

"Tom Ogilvy" wrote in message
...
Yes, but you can't break string integrity.

Instead of

s = "abcd _
efgh"

you have to do

s= "abcd" _
& "efgh"

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. I have a long formula in VBA:

ActiveCell.Formula = "=IF(ISBLANK(B" & cellrow & "),"""",IF(D" &
cellrow
&
"=""Yes"",LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),RIGHT(B" & cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow &
")))),LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),""."",RIGHT(B" &
cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow & "))))))"


When I try to fo the "space underscore" to wrap the code down to the
next
line, I get compile errors. Can I not use the _ to wrap text within a
formula?

Wrap text in VBA
 

When entering functions/formulas thru VBA
it's often lots simpler (certainly when working
with relative references) to use R1C1.

(and ofcourse assign them to FormulaR1c1)

then you dont need the cellrow at all and
you can simply enter the formula.

rngDest.formular1c1= "=IF(ISBLANK(RC2),"""", etc




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steph wrote :

Thanks Tom.

"Tom Ogilvy" wrote in message
...
Yes, but you can't break string integrity.

Instead of

s = "abcd _
efgh"

you have to do

s= "abcd" _
& "efgh"

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. I have a long formula in VBA:

ActiveCell.Formula = "=IF(ISBLANK(B" & cellrow & "),"""",IF(D" &
cellrow
&
"=""Yes"",LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),RIGHT(B" &
cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow &
")))),LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),""."",RIGHT(B" &
cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow & "))))))"


When I try to fo the "space underscore" to wrap the code down to
the
next
line, I get compile errors. Can I not use the _ to wrap text
within a formula?

Wrap text in VBA
 

Great suggestion - Thanks!

"keepITcool" wrote in message
ft.com...

When entering functions/formulas thru VBA
it's often lots simpler (certainly when working
with relative references) to use R1C1.

(and ofcourse assign them to FormulaR1c1)

then you dont need the cellrow at all and
you can simply enter the formula.

rngDest.formular1c1= "=IF(ISBLANK(RC2),"""", etc




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steph wrote :

Thanks Tom.

"Tom Ogilvy" wrote in message
...
Yes, but you can't break string integrity.

Instead of

s = "abcd _
efgh"

you have to do

s= "abcd" _
& "efgh"

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. I have a long formula in VBA:

ActiveCell.Formula = "=IF(ISBLANK(B" & cellrow & "),"""",IF(D" &
cellrow
&
"=""Yes"",LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),RIGHT(B" &
cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow &
")))),LOWER(CONCATENATE(LEFT(B" & cellrow & ",1),""."",RIGHT(B" &
cellrow
&
",LEN(B" & cellrow & ")-FIND("" "",B" & cellrow & "))))))"


When I try to fo the "space underscore" to wrap the code down to
the
next
line, I get compile errors. Can I not use the _ to wrap text
within a formula?