| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get date/time to wrap (format - wrap text doesn't work)? | Excel Discussion (Misc queries) | |||
| Excel 2007; even though "Wrap Text" is on the text does wrap? | Excel Discussion (Misc queries) | |||
| Wrap text doesn't wrap | Excel Worksheet Functions | |||
| Why won't text in cell wrap. Cell format set to wrap. | Excel Discussion (Misc queries) | |||
| Text not continuing to wrap for large block of text in Excel cell | Charts and Charting in Excel | |||
![Steph[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
