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

[jigglypuff]

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:

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)))))))))))))))))))))))))"

Thank you in advance.

 

[GCExcel]

Hi jigglypuff,

This seems to work on my side :

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 !

 

[Jonmo1]

Give this a try.

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)"

 

[jigglypuff]

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!

 

[Jonmo1]

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

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)"

 

[jigglypuff]

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).

 

[Jonmo1]

Great, glad to help.

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.