Simplifying complex excel formula with VBA

Question

I have a macro that is generally slow due to overuse of LOOKUP formulas. I want to insert some VBA variables to speed these up. I am currently working on speeding up the formula below:in Excel:

=IF(ISNA(MATCH(A2,Summary!B:B,0)),"n",I2-((I2/LOOKUP(2,1/(I:I<>""),I:I))*VLOOKUP(A2,Summary!$G$10:$H$902,2,FALSE)))

in VBA:

"=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-((RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1]))*VLOOKUP(RC[-9],Summary!R10C7:R902C8,2,FALSE)))"

The portion I need to replace is LOOKUP(2,1/(C[-1]<>""""),C[-1]). All this does is reference the last non empty cell in column I. Right now I have the following code to return the address of the last cell in VBA

Sub FormulaTest()
Set lRow = Range("I1").SpecialCells(xlCellTypeLastCell).Address

End Sub

I am trying to figure out how to implement this "lRow" into the VBA code for the formula. Can anyone steer me in the right direction?

**EDIT 1 Please see Fernando's comment below. He has the right idea however the solution is still off a bit. Ill try to explain it better in a few comments: First off, The first row is always a title row, the last row is always a sum row, the current tab is the "Sales" tab, and the amount of rows in any given Sales tab will vary (could be I1:I59, could be I:1:I323).

In this example I1 is a row title and I59 is the sum of I2:I58. Rows I2:I58 are dollar amounts. My macro places this formula in J2:J58. This formula takes each row's dollar amount (I2:I58) as a percentage of the total (I59) and multiplies it by an input amount on the Summary tab (the VLOOKUP). This amount is then subtracted proportionately from the dollar value in column I with the J cell showing the result.

I am looking to eliminate the need for the LOOKUP function (selects last non empty cell) within my formula above: LOOKUP(2,1/(C[-1]<>""""),C[-1]).

**EDIT 2 Fernando's solution worked. Thank you all for your input

Where are you putting these formulas? "=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-((RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1]))*VLOOKUP(RC[-9],Summary!R10C7:R902C8,2,FALSE)))" Just in one column? — QHarr
These formulas are placed in column J for any row with cell A matching a corresponding cell on the Summary tab — carter
Do you really need formulas on your worksheet? You might be better off accomplishing whatever it is you are doing solely in VBA. Read your range into a variant array; then go through the array to create your results; then write it back to the worksheet. — Ron Rosenfeld
Your question says you use INDIRECT, but your example doesn't have INDIRECT in it. You also need to give more information to get the best results from this forum. What does the formula do? How many items in the lookup list? How long is it currently taking to calculate? Moving the logic to VBA might speed it it, but I'm almost certain that if you provide answers to my questions then I could give you a lightning fast formula or an approach using PivotTables that is quicker and simpler than a VBA approach. — jeffreyweir
Also, you've only posted part of your code. It's possible that part of your slow speed symptoms are to do with how you are populating the worksheet with these formulas. Are you writing the formula to one cell at a time? You could also radically speed up the formula by using IFERROR to replace the need for IF(ISNA and you could probably use an array formula to process the entire range at once. But again, you need to further elaborate on what you are trying to do, what your data layout looks like, and how the rest of your code is structured. — jeffreyweir

Fernando J. Rivera Fernando J. Rivera · Accepted Answer · 2017-10-31T16:54:31

This would return the last non-empty row in column I

with Worksheets("Summary")
lRow = .Cells(.Rows.Count, "I").End(xlUp).Row
end with

So your code would be

sub testy
dim lRow as long
with Worksheets("Summary")
lRow = .Cells(.Rows.Count, "I").End(xlUp).Row
end with

"=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-_
((RC[-1]/R"&lRow&"C[-1])*VLOOKUP(RC[-9],Summary!R10C7:R902C8,2,FALSE)))"

In your solution you're using xlCellTypeLastCell. This is very useful, but it calculates based on UsedRange, which may not be what you want. with this, if you have data up to row n and then you update the data and now you have less records, the last row with xlCellTypeLastCell will still be n, so be careful with that.

Simplifying complex excel formula with VBA

2 Answers