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

 

[steffiweffi]

Hi, Jake!

I considered a vlookup table, but the ranges on the table I'm required to work with are really weird. If you could explain how I can upload a picture, I can show you exactly what I'm working with.

Thanks!

 

[steffiweffi]

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

Oh... well, that's good to know!

 

[steffiweffi]

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

So you suggest typing it out while recording the macro?

 

[InfoH]

when converting it to VBA via the record method this is how it comes out for me:

    ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[7]C[1]=0,R[8]C[1]=0),""Invalid Input"",IF(AND(R[7]C[1]<=4,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C,IF(AND(R[7]C[1" & _
        "8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[2],IF(AND(R[7]C[1]>=100," & _
        "=0.188),'Laser Mark-Up'!R[-6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.5),'Laser Mark-Up'!RC,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0." & _
        " Mark-Up'!RC[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C" & _
        "D(R[7]C[1]<=4,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C,IF(AND(R[7]C[1]<=24,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[1],IF(AND(R[7]C[1]<" & _
        "[1]>=1),'Laser Mark-Up'!R[9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[3],""Invalid Input"")))))))))))))))))))))))))))))))))"

Obviously I can't check if that works because I don't have your other tabs, but it just returns Invalid Input for me, rather than any #Value errors

 

[steve the fish]

Was your activecell the same? Because otherwise its a different formula.

 

[steffiweffi]

when converting it to VBA via the record method this is how it comes out for me:

    ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[7]C[1]=0,R[8]C[1]=0),""Invalid Input"",IF(AND(R[7]C[1]<=4,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C,IF(AND(R[7]C[1" & _
        "8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[2],IF(AND(R[7]C[1]>=100," & _
        "=0.188),'Laser Mark-Up'!R[-6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.5),'Laser Mark-Up'!RC,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0." & _
        " Mark-Up'!RC[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C" & _
        "D(R[7]C[1]<=4,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C,IF(AND(R[7]C[1]<=24,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[1],IF(AND(R[7]C[1]<" & _
        "[1]>=1),'Laser Mark-Up'!R[9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[3],""Invalid Input"")))))))))))))))))))))))))))))))))"

Obviously I can't check if that works because I don't have your other tabs, but it just returns Invalid Input for me, rather than any #Value errors

I copied and pasted the above and for some reason the debugger is still highlighting the entire formula. Here is my macro in it's entirety (all I need it to do is to insert that formula into a single cell):

Sub testforroger()
'
' testforroger Macro
'


'
    Range("H5").Select
        ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[7]C[1]=0,R[8]C[1]=0),""Invalid Input"",IF(AND(R[7]C[1]<=4,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.105),'Laser Mark-Up'!R[-12]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C,IF(AND(R[7]C[1" & _
        "8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.12),'Laser Mark-Up'!R[-9]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.188),'Laser Mark-Up'!R[-6]C[2],IF(AND(R[7]C[1]>=100," & _
        "=0.188),'Laser Mark-Up'!R[-6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.313),'Laser Mark-Up'!R[-3]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]<=0.5),'Laser Mark-Up'!RC,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0." & _
        " Mark-Up'!RC[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.5),'Laser Mark-Up'!RC[3],IF(AND(R[7]C[1]<=4,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C,IF(AND(R[7]C[1]<=24,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]=0.625),'Laser Mark-Up'!R[3]C" & _
        "D(R[7]C[1]<=4,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C,IF(AND(R[7]C[1]<=24,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[1],IF(AND(R[7]C[1]<=99,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]<=0.875),'Laser Mark-Up'!R[6]C[3],IF(AND(R[7]C[1]<=4,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C,IF(AND(R[7]C[1]<=24,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[1],IF(AND(R[7]C[1]<" & _
        "[1]>=1),'Laser Mark-Up'!R[9]C[2],IF(AND(R[7]C[1]>=100,R[8]C[1]>=1),'Laser Mark-Up'!R[9]C[3],""Invalid Input"")))))))))))))))))))))))))))))))))"
End Sub

Maybe I inserted it wrong?

 

[steffiweffi]

Was your activecell the same? Because otherwise its a different formula.

Yes, my active cell is always the same, as well as the two cells that I am using for the initial input. Only the values with those cells change.

 

[steve the fish]

The reason the debugger is highlighting the line is that is incomplete. I said before the macro recorder wont work for long formula. It chops part of the formula off. As long as ive used excel it has. Look closely at the start and end of each line. Is this formula dragged down? Maybe try:

Sub Macro1()

ActiveCell.Formula = _
"=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"")))))))))))))))))))))))))))))))))"
    
End Sub

 

[steffiweffi]

The reason the debugger is highlighting the line is that is incomplete. I said before the macro recorder wont work for long formula. It chops part of the formula off. As long as ive used excel it has. Look closely at the start and end of each line. Is this formula dragged down? Maybe try:

Sub Macro1()

ActiveCell.Formula = _
"=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"")))))))))))))))))))))))))))))))))"
    
End Sub

Oh! I see what you're saying now. I'm taking a look at it and there was plenty chopped off. (Sorry, I'm a bit slow.) I'm gonna work on editing it and I'll let you know how it turns out.

 

[Jake Blackmore]

Ok so I picked apart your code and changed my mind. You NEED to learn Index and Match.

I created a table and I recommend you do the same. I created mine in C5:L9 on the same sheet but you can always move it.

From To 0.105 0.12 0.188 0.313 0.5 0.625 0.875 1
0 4 Laser Mark-Up'!B3 Laser Mark-Up'!B6 Laser Mark-Up'!B9 Laser Mark-Up'!B12 Laser Mark-Up'!B15 Laser Mark-Up'!B18 Laser Mark-Up'!B21 Laser Mark-Up'!B24
5 24 Laser Mark-Up'!C3 Laser Mark-Up'!C6 Laser Mark-Up'!C9 Laser Mark-Up'!C12 Laser Mark-Up'!C15 Laser Mark-Up'!C18 Laser Mark-Up'!C21 Laser Mark-Up'!C24
25 99 Laser Mark-Up'!D3 Laser Mark-Up'!D6 Laser Mark-Up'!D9 Laser Mark-Up'!D12 Laser Mark-Up'!D15 Laser Mark-Up'!D18 Laser Mark-Up'!D21 Laser Mark-Up'!D24
100 + Laser Mark-Up'!E3 Laser Mark-Up'!E6 Laser Mark-Up'!E9 Laser Mark-Up'!E12 Laser Mark-Up'!E15 Laser Mark-Up'!E18 Laser Mark-Up'!E21 Laser Mark-Up'!E24

Then I used the following code:
=IF(OR(C22=0,C23=0),"Invalid Input",INDEX(D6:K9,IFERROR(MATCH(C22,C6:C9,1),1),IFERROR(MATCH(C23,D5:K5,1),1)))

This first does the same check as you and asks if your entry cells that everything feeds off is empty because that would be invalid. References to 'Laser Mark-Up' is because I don't have that tab.

I then use Index which returns a reference from a grid all you have to do is give it the column and row number. That's where match comes into play. MOST IMPORTANTLY this works because you are trying to match a number and therefore you can sort / order your columns / rows in this little table by that criteria. It uses a binary search (less than or equal to) which is the 1 or final argument in my match formula. In the first example we are looking to find a number less than or equal to 100 so if we pick 50 then that falls in the range 25 to 99. Whilst 50 is more than 25 it's less than 100 so the formula knows to look on the 3rd row of the range.

Have a play and let me know your thoughts - it should make your VBA MUCH easier,
Jake

Hi, Jake!

I considered a vlookup table, but the ranges on the table I'm required to work with are really weird. If you could explain how I can upload a picture, I can show you exactly what I'm working with.

Thanks!