Line too long - How to separate it in 2 or more different lines?

jigglypuff

New Member
Hi guys, before I post here the code I am trying to input I need to state that I already tried the [space]+_ code and it didn't work, the Excel still asks me to an 'end of statement' in the second line.

There is no way to put this code in a different way without it flooding me in lag.

Here is the code:

Code:
``If Target.Column = 3 And Target.Row >= 8 Then Cells(Target.Row, 4).Formula = "=IF(C:C="","",IF(AND(A:A>=40909,A:A<=40939),(C:C/Sheet1!\$U\$56)*1000,IF(AND(A:A>40939,A:A<=40968),(C:C/Sheet1!\$U\$57)*1000,IF(AND(A:A>40968,A:A<=40999),(C:C/Sheet1!\$U\$58)*1000,IF(AND(A:A>40999,A:A<=41029),(C:C/Sheet1!\$U\$59)*1000,IF(AND(A:A>41029,A:A<=41060),(C:C/Sheet1!\$U\$60)*1000,IF(AND(A:A>41060,A:A<=41090),(C:C/Sheet1!\$U\$61)*1000,IF(AND(A:A>41090,A:A<=41121),(C:C/Sheet1!\$U\$62)*1000,IF(AND(A:A>41121,A:A<=41152),(C:C/Sheet1!\$U\$63)*1000,IF(AND(A:A>41152,A:A<=41182),(C:C/Sheet1!\$U\$64)*1000,IF(AND(A:A>41182,A:A<=41213),(C:C/Sheet1!\$U\$65)*1000,IF(AND(A:A>41213,A:A<=41243),(C:C/Sheet1!\$U\$66)*1000,IF(AND(A:A>41243,A:A<=41274),(C:C/Sheet1!\$U\$67)*1000,IF(AND(A:A>41274,A:A<=41305),(C:C/Sheet1!\$U\$39)*1000,IF(AND(A:A>41305,A:A<=41333),(C:C/Sheet1!\$U\$40)*1000,IF(AND(A:A>41333,A:A<=41364),(C:C/Sheet1!\$U\$41)*1000,IF(AND(A:A>41364,A:A<=41394),(C:C/Sheet1!\$U\$42)*1000,IF(AND(A:A>41394,A:A<=41425),(C:C/Sheet1!\$U\$43)*1000,IF(AND(A:A>41425,A:A<=41455),(C:C/Sheet1!\$U\$44)*1000,IF(AND(A:A>41455,A:A<=41486),(C:C/Sheet1!\$U\$45)*1000,IF(AND(A:A>41486,A:A<=41517),(C:C/Sheet1!\$U\$46)*1000,IF(AND(A:A>41517,A:A<=41547),(C:C/Sheet1!\$U\$47)*1000,IF(AND(A:A>41547,A:A<=41578),(C:C/Sheet1!\$U\$48)*1000,IF(AND(A:A>41578,A:A<=41608),(C:C/Sheet1!\$U\$49)*1000,IF(AND(A:A>41608,A:A<=41639),(C:C/Sheet1!\$U\$50)*1000,FALSE)))))))))))))))))))))))))"``

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi jigglypuff,

This seems to work on my side :

Code:
``````Cells(Target, 4).Formula = "=IF(C:C="","",IF(AND(A:A>=40909,A:A<=40939)," & _   "(C:C/Sheet1!\$U\$56)*1000,IF(AND(A:A>40939,A:A<=40968),(C:C/Sheet1!\$U\$57)*1000,IF(AND(A:A>40968,A:A<=40999)," & _
"(C:C/Sheet1!\$U\$58)*1000,IF(AND(A:A>40999,A:A<=41029),(C:C/Sheet1!\$U\$59)*1000,IF(AND(A:A>41029,A:A<=41060)," & _
"(C:C/Sheet1!\$U\$60)*1000,IF(AND(A:A>41060,A:A<=41090),(C:C/Sheet1!\$U\$61)*1000,IF(AND(A:A>41090,A:A<=41121)," & _
"(C:C/Sheet1!\$U\$62)*1000,IF(AND(A:A>41121,A:A<=41152),(C:C/Sheet1!\$U\$63)*1000,IF(AND(A:A>41152,A:A<=41182)," & _
"(C:C/Sheet1!\$U\$64)*1000,IF(AND(A:A>41182,A:A<=41213),(C:C/Sheet1!\$U\$65)*1000,IF(AND(A:A>41213,A:A<=41243)," & _
"(C:C/Sheet1!\$U\$66)*1000,IF(AND(A:A>41243,A:A<=41274),(C:C/Sheet1!\$U\$67)*1000,IF(AND(A:A>41274,A:A<=41305)," & _
"(C:C/Sheet1!\$U\$39)*1000,IF(AND(A:A>41305,A:A<=41333),(C:C/Sheet1!\$U\$40)*1000,IF(AND(A:A>41333,A:A<=41364)," & _
"(C:C/Sheet1!\$U\$41)*1000,IF(AND(A:A>41364,A:A<=41394),(C:C/Sheet1!\$U\$42)*1000,IF(AND(A:A>41394,A:A<=41425)," & _
"(C:C/Sheet1!\$U\$43)*1000,IF(AND(A:A>41425,A:A<=41455),(C:C/Sheet1!\$U\$44)*1000,IF(AND(A:A>41455,A:A<=41486)," & _
"(C:C/Sheet1!\$U\$45)*1000,IF(AND(A:A>41486,A:A<=41517),(C:C/Sheet1!\$U\$46)*1000,IF(AND(A:A>41517,A:A<=41547)," & _
"(C:C/Sheet1!\$U\$47)*1000,IF(AND(A:A>41547,A:A<=41578),(C:C/Sheet1!\$U\$48)*1000,IF(AND(A:A>41578,A:A<=41608)," & _
"(C:C/Sheet1!\$U\$49)*1000,IF(AND(A:A>41608,A:A<=41639),(C:C/Sheet1!\$U\$50)*1000,FALSE)))))))))))))))))))))))))"``````

Cheers !

Give this a try.

Code:
``If Target.Column = 3 And Target.Row >= 8 Then Cells(Target.Row, 4).Formula = "=IF(C" & Target.Row & "="""","""",C" & Target.Row & "/INDEX(IF(YEAR(C" & Target.Row & ")=2013,Sheet1!\$U\$39:\$U\$50,Sheet1!\$U\$56:\$U\$67),MONTH(C" & Target.Row & "))*1000)"``

Thank you very much, GCExcel and Jonmo, the code posted by GCExcel worked perfectly, I just had to correct some mistakes committed by myself e.g: insert two more "" in the first line!

Unfortunately although very 'elegant' your code seemed not to work Jonmo =p but I will see it later and see if I can take advantage of it, it was a very nice try!

Unfortunately although very 'elegant' your code seemed not to work Jonmo =p but I will see it later and see if I can take advantage of it, it was a very nice try!

In what way did it not work?
I realize the resulting formula is a 'Drastic' change from the original formula.
But given the logic of the original formula, the one I posted should produce the same results.

I think I see the mistake I made, it wants to lookup the Date in Column A, but do the Division by column C.
I did both on column C.

Try this

Code:
``If Target.Column = 3 And Target.Row >= 8 Then Cells(Target.Row, 4).Formula = "=IF(C" & Target.Row & "="""","""",C" & Target.Row & "/INDEX(IF(YEAR(A" & Target.Row & ")=2013,Sheet1!\$U\$39:\$U\$50,Sheet1!\$U\$56:\$U\$67),MONTH(A" & Target.Row & "))*1000)"``

Now it seems to be working! I didn't check if the outputs match exactly with the correct ones but now your formula is changing the results when I change the date (it wasn't before).

Out of curiosity, do you have more than 2 years of data?
That can be modified to extend beyond just 2 years, as long as the pattern is the same (5 rows between each set of 12 rows on Sheet1), and the data descends in years going down.

Replies
27
Views
588
Replies
8
Views
724
Replies
0
Views
433
Replies
3
Views
7K

1,206,759
Messages
6,074,777
Members
446,087
Latest member
PinkFloyd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back