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

 

[shg]

Wow, nice thumb! Thanks, Ruddles.

 

[mohdomair440]

Please can anyone help me in creating a VB code for the below. It should be pasted in Cell "MH1" when I run the macro

IF(CONCATENATE(LI1,MK1)="RRUS-11LTE 700",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_7"&E1&"_1",IF(CONCATENATE(LI1,MK1)="RRUS-E2LTE 700",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_7"&E1&"_2_E",IF(CONCATENATE(LI1,MK1)="B14 4478LTE 700",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_7"&E1&"_3_F",IF(CONCATENATE(LK1,MK1)="RRUS-11LTE 850",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_8"&E1&"_1",IF(CONCATENATE(LK1,MK1)="RRUS-12LTE 850",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_8"&E1&"_1",IF(CONCATENATE(LM1,MK1)="RRUS-11LTE 1900",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_9"&E1&"_1",IF(CONCATENATE(LM1,MK1)="RRUS-12LTE 1900",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_9"&E1&"_1",IF(CONCATENATE(LM1,MK1)="RRUS-32 B2LTE 1900",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_9"&E1&"_1",IF(CONCATENATE(LO1,MK1)="RRUS-11LTE AWS",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_2"&E1&"_1",IF(CONCATENATE(LO1,MK1)="RRUS-12LTE AWS",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_2"&E1&"_1",IF(CONCATENATE(LO1,MK1)="RRUS-32 B66LTE AWS",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_2"&E1&"_1",IF(CONCATENATE(LQ1,MK1)="RRUS-32LTE WCS",LEFT(B1,2)&"L0"&RIGHT(B1,4)&"_3"&E1&"_1"))))))))))))

 

[Ruddles]

Generally it would be Range("MH1")="somestring" *but* strings containing doublequotes need the doublequotes to be doubled up, so maybe:-

Range("MH1")="=IF(CONCATENATE(LI1,MK1)=""RRUS-11LTE 700"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_7""&E1&""_1"",IF(CONCATENATE(LI1,MK1)=""RRUS-E2LTE 700"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_7""&E1&""_2_E"",IF(CONCATENATE(LI1,MK1)=""B14 4478LTE 700"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_7""&E1&""_3_F"",IF(CONCATENATE(LK1,MK1)=""RRUS-11LTE 850"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_8""&E1&""_1"",IF(CONCATENATE(LK1,MK1)=""RRUS-12LTE 850"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_8""&E1&""_1"",IF(CONCATENATE(LM1,MK1)=""RRUS-11LTE 1900"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_9""&E1&""_1"",IF(CONCATENATE(LM1,MK1)=""RRUS-12LTE 1900"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_9""&E1&""_1"",IF(CONCATENATE(LM1,MK1)=""RRUS-32 B2LTE 1900"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_9""&E1&""_1"",IF(CONCATENATE(LO1,MK1)=""RRUS-11LTE AWS"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_2""&E1&""_1"",IF(CONCATENATE(LO1,MK1)=""RRUS-12LTE AWS"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_2""&E1&""_1"",IF(CONCATENATE(LO1,MK1)=""RRUS-32 B66LTE AWS"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_2""&E1&""_1"",IF(CONCATENATE(LQ1,MK1)=""RRUS-32LTE WCS"",LEFT(B1,2)&""L0""&RIGHT(B1,4)&""_3""&E1&""_1""))))))))))))"

 

[Ruddles]

By the way, you'll probably received more responses if you post a new question in a brand new thread, because that way people will see there are zero replies and they're more likely to look at it rather than a six-year-old thread which has just received one new reply.

If you see what I mean...