Solved: line continuation on formula?

[YellowLabPro]

How to break this up onto multiple lines?
[VBA]
"=IF(G132,IF(MOD(CEILING(G132*2,5),10)=0,CEILING(G132*2,5)-1+0.99,CEILING(G132*2,5)+0.99),"""")"
[/VBA]

[Bob Phillips]

"=IF(G132,IF(MOD(CEILING(G132*2,5),10)=0," & _
"CEILING(G132*2,5)-1+0.99," & _
"CEILING(G132*2,5)+0.99),"""")"

[YellowLabPro]

Thanks Bob.
So splitting it up by using a double quote, then & then _.... got it.

thanks!

[Bob Phillips]

Yeah. Because it is a text string, you have to close it on one lin, then concatenate the next piece using & before the lin continuation.

You are effectively splitting it into three strings and then concatenating them back together :-)

[YellowLabPro]

I have this formula that I have been messing around w/ to place it in code. I cannot find a good example or rule to apply where quotes go to properly write.

"=IF(G132,IF(MOD(CEILING(G132*2,5),10)=0,CEILING(G132*2,5)-1+0.99,CEILING(G132*2,5)+0.99),"""")"

I need to change G132 to cells or a range as well as add quotes in the proper location throughout the formula.
I did get the R1C1 formula to work, but would like to learn this method too.
I have tried several variations of the below line, non quite right.
[vba]
"=IF(G"" & i "",IF(MOD(CEILING(G"" & i "",*2,5),10)=0,CEILING(G"" & i "",*2,5)-1+0.99,CEILING(G"" & i "",132*2,5)+0.99),"""")"
[/vba]

[YellowLabPro]

Yeah. Because it is a text string, you have to close it on one lin, then concatenate the next piece using & before the lin continuation.

You are effectively splitting it into three strings and then concatenating them back together :-)

This is actually starting to make sense.....
Finally...
Better late than never

thanks again

[Bob Phillips]

Again, when you want to enclose a variable, you first close out the string (one double quotes) concatenate the variable and concatenate the rest of the string but opening it with another double quotes

[vba]

"some text and a " & variable & " then some more"
[/vba]

Double quotes comes into play when you want to embed quotes in a string

[vba]

"IF G" & variable & " = ""some value"" THEN ..."
[/vba]

which of course gets odd looking if testing for empty

[vba]

"IF G" & variable & "= """" THEN ..."
[/vba]

or if you enclose the variable value within quotes becuase it is text as well, then you have three (two to setup a quotes in your string, one to close out

[vba]

"IF G2=""" & variable & """, ..."
[/vba]

In summary, your situation

[vba]

"=IF(G" & i ",IF(MOD(CEILING(G" & i ",*2,5),10)=0,CEILING(G" & i ",*2,5)-1+0.99,CEILING(G" & i ",132*2,5)+0.99),"""")"
[/vba]

You dont need two double-quotes as you are not trying to embed a quotes in the string, you just want to close and open your text string to incorporate a variable value. You only need two doubles at the end (twice) where you want the default to be blank (which you had yourself).

[YellowLabPro]

Give me a little time to run through this. This is perfect. Thanks for the explanation-
One thing that would be a nice debugging feature is if it highlighted what was wrong not the entire line, ( I know it will not be so, but just wishful thinking).
I have started removing parts to see which part would be in err, and which is not. That is about the only way I have figured out how to step through an expression.

Thanks again for the help and patience....

[Bob Phillips]

Doug,

I know what you mean, but as you say wishful thinking.

When I have that sort of problem, I always setup a string variable with the value, and I find it easier to do a shift-F9 on that, because it might load the variable whereas it doesn't put it in a cell.

[YellowLabPro]

Bob,
I spent some time reading and thinking through your explanation. I have set up and worked through a few different scenarios. But in solving this one, I cannot work it out. This will aid in my other questions I had previously prepared and hopefully elimnate some of them.
It seems simple enough-

Original formula in worksheet
=IF(AE132="","$0.00",(AG131+AF132))

My expression in VBA:

[vba]Range("AG" & j).Formula= "=IF(AE" & j & "="""","$0.00",(AG" & j -1 & "+AF" & j & "))"[/vba]

Thank you....

[Bob Phillips]

As I said Doug, create a string with the text before passing it to the formula property, aids debugging.

But why are you testing for "$0.00"? The value may LOOK like that, but isn't it just zero in reality? So your formula should be

[vba]

Range("AG" & j).Formula= "=IF(AE" & j & "="""",0,(AG" & j -1 & "+AF" & j & "))"
[/vba]

But your problem is because you are not escaping the quotes around the text $0.00, my second example in #7 above. It should be

[vba]

Range("AG" & j).Formula= "=IF(AE" & j & "="""",""$0.00"",(AG" & j -1 & "+AF" & j & "))"
[/vba]

[YellowLabPro]

Bob,
As I wrote in post# 10, (not to be redundant), I spent time reading and thinking through your explanation had been studying the different examples you have provided; I had started to post, then held off to do some more testing. I wanted to check what resulted to help me understand better, before posting back too quickly.
This has proven worthy as some questions have been elimnated now.

You pointed out in my last call for help:

create a string with the text before passing it to the formula property

What do you mean by this? Sorry I don't know what is meant by create a string w/ text and where to place this to test. (This is referenced below, it was one of my inital follow up questions).

* Marked items are the points I fail to comprehend:

1) Going back into specific parts- in red: could you elaborate or provide a short example?
.Setting up a string variable with a value?*
.Shift-F9 is what, setting up a Watch? I have yet to learn how to use Watches debugging.
.Might load the variable, whereas does not place in a cell? Not following, probably b/c it is related to above ideas.

When I have that sort of problem, I always setup a string variable with the value, and I find it easier to do a shift-F9 on that, because it might load the variable whereas it doesn't put it in a cell.

2) Where you speak of a text variable-

or if you enclose the variable value within quotes becuase it is text as well, then you have three (two to setup a quotes in your string, one to close out

[vba]"IF G2=""" & variable & """, ..." [/vba]
.Text variable, is not registering what is meant by a text variable.*
.Don't follow here, though have seen three quotes in prior work, and cannot recall what that might be.

The last point I would like to get your thought on was the inital remark about enclosing a variable.

Again, when you want to enclose a variable

Are we enclosing variables or excluding them from the string, enclosing and setting off string items w/ quotes. I probably have this backwards as usual.... it is remaining side effects dislexia....

Thanks for the help on this. This is something I have wanted to tackle for quite some time now. I strongly dislike using a procedure or method and doing it only by imitation rather than reasoning it out. Today has already been a great lesson.