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]

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

Oh goodness. I had no idea macros could get this complex. I'll play around with this and try to learn how this works, but it seems like it's a much simpler solution to what I've been trying to do. Thanks!

 

[steffiweffi]

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

I just made the table and inserted the code, but it doesn't seem to register that "0 4","5 24","25 99", and "100 +" are ranges. Instead, I keep changing the values in my two reference cells (C22 and C23) and it just keeps giving me the first value in the array (the cell where "0 4" and "0.105" meet).

 

[Jonmo1]

I agree, the whole original formula needs a restructuring.
All those IF(AND's are what's making it so long.

Try this one

=IF(OR(C22=0,C23=0),"Invalid Input",IFERROR(INDEX('Laser Mark-Up'!B3:E24,(MATCH(C23,{0.105,0.12,0.189,0.314,0.501,0.626,0.876,1})-1)*3+1,MATCH(C22,{0,5,25,100})),"Invalid Input"))