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

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60
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

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

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60

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
 

James02

Board Regular
Joined
Nov 6, 2014
Messages
108

ADVERTISEMENT

dude that's a serious formula
 

steffiweffi

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,275
Office Version
  1. 365
Platform
  1. Windows
The macro recorder doesn't work for long formula. It chops off a few characters at each line feed!
 

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,861
Messages
5,598,493
Members
414,243
Latest member
Shockpulsar

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
Top