Formula Too Long?

steffiweffi

New Member
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

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:
Thanks for the reply! I posted the formula below.

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

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

dude that's a serious formula

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.

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

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

Replies
12
Views
410
Replies
1
Views
175
Replies
0
Views
205
Replies
22
Views
1K
Replies
4
Views
229

1,212,148
Messages
6,106,237
Members
448,008
Latest member
D_SQUARED

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