Excel VBA - Checking array from adjacent cell

Question

I have an array setup in D13:D19 where values are entered by the user. I'm modifying the formulas in my sheet to not be dependant on all cells being input.

In the column adjacent to the array I have it showing the change from the previous cell like so:

=IF([@[Weight (lbs)]]="","",[@[Weight (lbs)]]-D17)

Where D17 is the cell above the current row [row 18].

I need to redo this so that it looks at the adjacent cell for an integer, if an integer exists then it checks the array above it for the last value and uses that value in the formula. If no integer exists the cell remains blank.

Example:

Cell D15 = 155 
Cell D16 = 155 
Cell D17 = BLANK 
Cell D18 = 145

In E18, the formula will need to look at D18 to see if there is an integer, if yes then it will then look for the last filled cell in the array which in this example is D16. Then it will subtract the last entered cell from D18 [D18-D16].

The last filled cell above D18? What if D18 is not an integer, and could it be a decimal? — SJR
Yes. Last filled cell that occurs above D18, which in the example is D16 due to D17 being skipped. Any number in the array D13:D19 could be a decimal, you're correct. — BetaOp9

Scott Craner Scott Craner · Accepted Answer · 2017-03-01T14:33:01

Put this in place of D17 in your formula:

INDEX(D:D,MATCH(1E+99,D1:INDEX(D:D,ROW()-1)))

It will return the value of the last cell with a number above the current row.

Excel VBA - Checking array from adjacent cell

1 Answers