Formula Too Long?

steffiweffi

New Member
Joined
Jun 8, 2015
Messages
12
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.)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
Haha... yeah. It's a bit of a disaster. This is what it looks like when not in VBA:

Code:
=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. :)
 
Upvote 0
The macro recorder doesn't work for long formula. It chops off a few characters at each line feed!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top