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
– SJR, Commented Feb 28, 2017 at 22:29
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
– BetaOp9, Commented Mar 1, 2017 at 7:27

Scott Craner · Accepted Answer · 2017-03-01 14:33:01Z

1

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.

answered Mar 1, 2017 at 14:33

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

BetaOp9 Over a year ago

Worked perfectly. Thanks again!

BetaOp9 Over a year ago

One question, am I able to limit the index to the D13:D19 ranges instead of the full column?

Scott Craner Over a year ago

Yes, Just change the both D:D to your range and the D1 to D13

Scott Craner Over a year ago

Try this new formula, you may need to put the formula in manually: =INDEX($D$13:$D$19,MATCH(1E+99,$D$13:INDEX(D$1:$D$19,ROW()-1))) because the comments sometimes adds unprintable characters.

Scott Craner Over a year ago

It is working for me, without problem. So from here I do not know what to tell you.

|

Collectives™ on Stack Overflow

Excel VBA - Checking array from adjacent cell

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related