Formula Too Long?

[steffiweffi]

Hey, everyone. I'm new to writing macros (and this site as well!), and I have an issue where my formula seems to be too long. I read some other posts on this site, including one that suggested breaking it up by using " _ &", but that doesn't seem to work. Anyone else have tips?

(Also, I have no idea how to post the code I have.)

 

[InfoH]

Hi,

Just copy and paste the line you have which is too long (where debug is saying you have an error)

the _ suggestion is correct, that essentially enables you to continue your macro on the next line.. no need for the & though.

 

[steffiweffi]

This is the formula I currently have. Deleting the & still caused the whole formula to turn red and I'm not entirely sure why.



ActiveCell.FormulaR1C1 = "=IF(OR(R[17]C[-5]=0,R[18]C[-5]=0),""Invalid Input"",IF(AND(R[17]C[-5]<=4,R[18]C[-5]=0.105),'Laser Mark-Up'!R[-2]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]=0.105),'Laser Mark-Up'!R[-2]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]=0.105),'Laser Mark-Up'!R[-2]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]=0.105),'Laser Mark-Up'!R[-2]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]=0.12),'Laser Mark-Up'!R[1]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]=0.12),'Laser Mark-Up'!R[1]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]=0.12),'Laser Mark-Up'!R[1]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]=0.12),'Laser Mark-Up'!R[1]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]<=0.188),'Laser Mark-Up'!R[4]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]<=0.188),'Laser Mark-Up'!R[4]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]<=0.188),'Laser Mark-Up'!R[4]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]<=0.188),'Laser Mark-Up'!R[4]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]<=0.313),'Laser Mark-Up'!R[7]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]<=0.313),'Laser Mark-Up'!R[7]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]<=0.313),'Laser Mark-Up''!R[7]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]<=0.313),'Laser Mark-Up'!R[7]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]<=0.5),'Laser Mark-Up'!R[10]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]<=0.5),'Laser Mark-Up'!R[10]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]<=0.5),'Laser Mark-Up'!R[10]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]<=0.5),'Laser Mark-Up'!R[10]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]=0.625),'Laser Mark-Up'!R[13]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]=0.625),'Laser Mark-Up'!R[13]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]=0.625),'Laser Mark-Up'!R[13]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]=0.625),'Laser Mark-Up'!R[13]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]<=0.875),'Laser Mark-Up'!R[16]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]<=0.875),'Laser Mark-Up'!R[16]C[-5],IF(AND(R[17]C[-5]<=99,R[18]C[-5]<=0.875),'Laser Mark-Up'!R[16]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]<=0.875),'Laser Mark-Up'!R[16]C[-3]," _
& "IF(AND(R[17]C[-5]<=4,R[18]C[-5]>=1),'Laser Mark-Up'!R[19]C[-6],IF(AND(R[17]C[-5]<=24,R[18]C[-5]>=1),'Laser Mark-Up'!R[19]C[,IF(AND(R[17]C[-5]<=99,R[18]C[-5]>=1),'Laser Mark-Up'!R[19]C[-4],IF(AND(R[17]C[-5]>=100,R[18]C[-5]>=1),'Laser Mark-Up'!R[19]C[-3],""Invalid Input"")))))))))))))))))))))))))))))))))"



Edit: My formula is probably a lot messier than it needs to be, but I'm still learning. What I'm trying to do is have this formula look up a value on a table on the sheet "Laser Mark-Up" depending on the two variables that are put into designated cells.

 

[steffiweffi]

Thanks for the reply! I posted the formula below.

 

[InfoH]

ADVERTISEMENT

wow hehe

can you also paste me what the formula looks like when not in VBA.. so essentially what your trying to convert into VBA

A nice tip for seeing how to correctly format things.. if you 'record' a macro and all you do while recording is input that formula into a cell and press enter.. stop the macro and check your VBA code for that macro it'll show you exactly how it inputs that into VBA and from there you can pick it apart to learn which bit does what.. that's how I learnt a lot of VBA

 

[Jake Blackmore]

Seems like lots of If statements - have you considered a Vlookup table?

 

[James02]

ADVERTISEMENT

dude that's a serious formula

 

[steffiweffi]

Haha... yeah. It's a bit of a disaster. This is what it looks like when not in VBA:

=IF(OR(C22=0,C23=0),"Invalid Input",IF(AND(C22<=4,C23=0.105),'Laser Mark-Up'!B3,IF(AND(C22<=24,C23=0.105),'Laser Mark-Up'!C3,IF(AND(C22<=99,C23=0.105),'Laser Mark-Up'!D3,IF(AND(C22>=100,C23=0.105),'Laser Mark-Up'!E3,IF(AND(C22<=4,C23=0.12),'Laser Mark-Up'!B6,IF(AND(C22<=24,C23=0.12),'Laser Mark-Up'!C6,IF(AND(C22<=99,C23=0.12),'Laser Mark-Up'!D6,IF(AND(C22>=100,C23=0.12),'Laser Mark-Up'!E6,IF(AND(C22<=4,C23<=0.188),'Laser Mark-Up'!B9,IF(AND(C22<=24,C23<=0.188),'Laser Mark-Up'!C9,IF(AND(C22<=99,C23<=0.188),'Laser Mark-Up'!D9,IF(AND(C22>=100,C23<=0.188),'Laser Mark-Up'!E9,IF(AND(C22<=4,C23<=0.313),'Laser Mark-Up'!B12,IF(AND(C22<=24,C23<=0.313),'Laser Mark-Up'!C12,IF(AND(C22<=99,C23<=0.313),'Laser Mark-Up'!D12,IF(AND(C22>=100,C23<=0.313),'Laser Mark-Up'!E12,IF(AND(C22<=4,C23<=0.5),'Laser Mark-Up'!B15,IF(AND(C22<=24,C23<=0.5),'Laser Mark-Up'!C15,IF(AND(C22<=99,C23<=0.5),'Laser Mark-Up'!D15,IF(AND(C22>=100,C23<=0.5),'Laser Mark-Up'!E15,IF(AND(C22<=4,C23=0.625),'Laser Mark-Up'!B18,IF(AND(C22<=24,C23=0.625),'Laser Mark-Up'!C18,IF(AND(C22<=99,C23=0.625),'Laser Mark-Up'!D18,IF(AND(C22>=100,C23=0.625),'Laser Mark-Up'!E18,IF(AND(C22<=4,C23<=0.875),'Laser Mark-Up'!B21,IF(AND(C22<=24,C23<=0.875),'Laser Mark-Up'!C21,IF(AND(C22<=99,C23<=0.875),'Laser Mark-Up'!D21,IF(AND(C22>=100,C23<=0.875),'Laser Mark-Up'!E21,IF(AND(C22<=4,C23>=1),'Laser Mark-Up'!B24,IF(AND(C22<=24,C23>=1),'Laser Mark-Up'!C24,IF(AND(C22<=99,C23>=1),'Laser Mark-Up'!D24,IF(AND(C22>=100,C23>=1),'Laser Mark-Up'!E24,"Invalid Input")))))))))))))))))))))))))))))))))

The formula above works perfectly outside of VBA. And I quickly learned that I had to reformat it to work in the VBA. I did record a macro and input part of the above formula so I could mimic the formatting, but it was too long to type the whole thing out by hand. Maybe I reformatted it incorrectly? It just tells me the entire formula is wrong, so I couldn't find where.

Again, thanks for the help.

 

[steve the fish]

The macro recorder doesn't work for long formula. It chops off a few characters at each line feed!

 

[InfoH]

I find with long formula's like that it is best to do your formatting inside the recorded macro as editing that manually will throw your rows out in VBA and unless you correctly do the _'s at the end of each row it'll be messy..

as Jake said above though, a vlookup table may be your better option looking at that formula