# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### InfoH

##### Board Regular
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
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(RC[-5]=0,RC[-5]=0),""Invalid Input"",IF(AND(RC[-5]<=4,RC[-5]=0.105),'Laser Mark-Up'!R[-2]C[-6],IF(AND(RC[-5]<=24,RC[-5]=0.105),'Laser Mark-Up'!R[-2]C[-5],IF(AND(RC[-5]<=99,RC[-5]=0.105),'Laser Mark-Up'!R[-2]C[-4],IF(AND(RC[-5]>=100,RC[-5]=0.105),'Laser Mark-Up'!R[-2]C[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]=0.12),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]=0.12),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]=0.12),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]=0.12),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]<=0.188),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]<=0.188),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]<=0.188),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]<=0.188),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]<=0.313),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]<=0.313),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]<=0.313),'Laser Mark-Up''!RC[-4],IF(AND(RC[-5]>=100,RC[-5]<=0.313),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]<=0.5),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]<=0.5),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]<=0.5),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]<=0.5),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]=0.625),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]=0.625),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]=0.625),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]=0.625),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]<=0.875),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]<=0.875),'Laser Mark-Up'!RC[-5],IF(AND(RC[-5]<=99,RC[-5]<=0.875),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]<=0.875),'Laser Mark-Up'!RC[-3]," _
& "IF(AND(RC[-5]<=4,RC[-5]>=1),'Laser Mark-Up'!RC[-6],IF(AND(RC[-5]<=24,RC[-5]>=1),'Laser Mark-Up'!RC[,IF(AND(RC[-5]<=99,RC[-5]>=1),'Laser Mark-Up'!RC[-4],IF(AND(RC[-5]>=100,RC[-5]>=1),'Laser Mark-Up'!RC[-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:

#### steffiweffi

##### New Member
Thanks for the reply! I posted the formula below.

#### InfoH

##### Board Regular

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

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

#### James02

##### Board Regular

dude that's a serious formula

#### steffiweffi

##### New Member
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
The macro recorder doesn't work for long formula. It chops off a few characters at each line feed!

#### InfoH

##### Board Regular
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
4
Views
138
Replies
3
Views
252
Replies
39
Views
642
Replies
5
Views
371
Replies
47
Views
3K Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,871
Messages
5,834,150
Members
430,261
Latest member
quangtinator ### 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.

### Which adblocker are you using?    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