CODEWITHSUNDEEP
vbaexcelexcel-formula
carter
carter

Reputation: 75

Simplifying complex excel formula with VBA

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

Upvotes: 1

Views: 1108

Answers (2)

Fernando J. Rivera
Fernando J. Rivera

Reputation: 749

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.

Upvotes: 1

n8.
n8.

Reputation: 1738

Assuming that you are doing all your work on the active sheet, looking up to a "Summary" sheet: 

Sub fillCol()

    Dim aRow As Long, bRow As Long

    aRow = Cells(Rows.Count, "I").End(xlUp).Row
    bRow = Sheets("Summary").Cells(Rows.Count, "I").End(xlUp).Row

    Range("J2:J" & aRow).FormulaR1C1 = "=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-" _
                       & "((RC[-1]/" & aRow & ")*VLOOKUP(RC[-9],Summary!R10C7:R" & bRow & "C8,2,FALSE)))"

End Sub

You made need to change the columns which contain the contiguous range (in order to determine the last row)

Upvotes: 0

Related Questions