VBA Editor.. Long line / Line too long

[Thebatfink]

Hi,

Its probably really simple but I'm scratching my head. I am programically inserting formulas into workbooks, and the formula for this range of cells is quite long. It spills over a line in the VBA editor but for some reason I don't seem to be able to use the usual underscore to continue on the next line?!

For example

sht.Range("AE" & rnumber & "").Formula = "=IF(OR($I" & rnumber & "="""",$AD" & rnumber & "=""""),"""",IF($I" & rnumber & "=""BLAHBLAHBLAHBLAH"",""BLAHBLAHBLAH"",IF(LEFT($I" & rnumber & ",2)=""BLAH"",""BLAHBLAH"",IF($I" & rnumber & "-$AD" & rnumber & "<=-1,""Yes"",""No"".......

its obviously longer than that but if I put for example the underscore in and some quotes

sht.Range("AE" & rnumber & "").Formula = "=IF(OR($I" & rnumber & "="""",$AD" & rnumber & "=""""),"""",IF($I" & rnumber & "=""BLAHBLAHBLAHBLAH"",""BLAHBLAHBLAH"",IF(LEFT($I" & rnumber & ",2)" _
"=""BLAH"",""BLAHBLAH"",IF($I" & rnumber & "-$AD" & rnumber & "<=-1,""Yes"",""No"".......

it doesn't like it at all. I'd appreciate it if someone can kick me in the right direction!

Thanks!
Batfink

 

[JamesW]

Hi,

You don't need the " at the end of line 1 and the start of line 2.

 

[CharlesChuckieCharles]

Try breaking it on an "IF( " as in

sht.Range("AE" & rnumber & "").Formula = "=IF(OR($I" & rnumber & "="""",$AD" & rnumber & "=""""),"""", _
IF($I" & rnumber & "=""BLAHBLAHBLAHBLAH"",""BLAHBLAHBLAH"",IF(LEFT($I" & rnumber & ",2)" "=""BLAH"",""BLAHBLAH"",IF($I" & rnumber & "-$AD" & rnumber & "<=-1,""Yes"",""No"".......

 

[Thebatfink]

Oh my.. I'm sure every now and again 99% of my brain cells go dormant.

Thanks for the help guys. Sorry to be so stupid!

 

[Ruddles]

Have you got this working okay now?

 

[Thebatfink]

Actually no. It still doesn't work, just tried it. Thats why I started putting quotes in because I suspect as its in quotes to begin with, the underscore isn't seen as normal.

sht.Range("AG" & rnumber & "").Formula = "=IF(R6="""", ""BLAHBLAH"",""BLAH"")"

simple one above ..

sht.Range("AG" & rnumber & "").Formula = "=IF(R6="""", _
    ""BLAHBLAH"",""BLAH"")"

this doesnt work. Compile error - expected end of statement. I suspect its because what I'm breaking is within quotes (like why I need to double up all quotes in the formula etc). I just can't get my head round how the syntax should be.

Thanks!

 

[Ruddles]

Okay, the answer is: you can't break a line of VBA code inside a string. If you want to break a line inside a string you have to terminate the first portion with ", insert the underscore, then commence the next line with an ampersand and a new opening quote for the second portion of the string.

Using your example:-

sht.Range("AG" & rnumber & "").Formula = "=IF(R6="""",""BLAHBLAH"",""BLAH"")"

becomes:-

sht.Range("AG" & rnumber & "").Formula = "=IF(R6="""",""BLAHBL[COLOR=red][B]" _[/B][/COLOR]
[COLOR=red][B]  & "[/B][/COLOR][COLOR=#000000]AH"",""BLAH"")"

[/COLOR]
or:-

sht.Range("AG" & rnumber & "").Formula = "=IF(R6="""",""BLAHBLAH""[COLOR=red][B]" _[/B][/COLOR]
[COLOR=red][B]  & "[/B][/COLOR],""BLAH"")"

If you can't get it to work, post the entire line of code - without any breaks in it - and I'll take a look at it later tonight when I get home.

 

[shg]

After you resolve your syntax issue, I find it easier when building a complex formula to assign it to a string variable. You can print it to the Immediate window and see if it's well-formed, rather than just getting an uninformative 1004 error.

    Dim sFrm        As String
 
    sFrm = "= big complicated formula spread over " & _
           "several lines"
    Debug.Print sFrm
    Range("A1").Formula = sFrm

 

[Thebatfink]

Morning,

Thanks Ruddles, that's solved my problem works well now, much appreciated thanks for your time!

Shg, I'm all for tips from the folks in the know, I'll give that a try, thanks very much!

Cheers
Batfink

 

[Ruddles]

I find it easier when building a complex formula to assign it to a string variable. You can print it to the Immediate window and see if it's well-formed, rather than just getting an uninformative 1004 error.