Find last calculated value in column of formulas

Hi
I've got a workbook that includes a Check Register worksheet. Column K of the Check Register worksheet has formulas that maintain a running current balance. So Column K has numbers (calculated) from K12 down to the last transaction (K461 is this instance). Cells K462 to K2000 contain formulas awaiting to be calculated.
I've written a procedure to "Create New Year" and in the process, I want to capture the value of the last calculated value in Column K from the old year. I cobbled code from different websites and have come up with this code

Please Login or Register  to view this content.

This code works but I have no idea why. I assume it has to do with

Please Login or Register  to view this content.

but I don't know what it means or what it's doing. Can one of you good people with a knowledge of higher math tell me what's going on with this line of code? Your help will be highly appreciated.
John

Have you tried the help on Lookup?

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

As long as all the values in your lookup table < 99^99 it will give you the largest value. As all values are sorted ascending it will give you the last value.

In most code you'll see that

Please Login or Register  to view this content.

will give you the last cell in column K:K as well.

Thank you for your reply. I believe I've tried this

In most code you'll see that
Code:
Cells(cells.rows.count, "K").end(xlup)will give you the last cell in column K:K as well.

and it picked up the last cell with a formula (K2000 in my case). However, I will try it again.
So, are you telling me here

As long as all the values in your lookup table < 99^99 it will give you the largest value. As all values are sorted ascending it will give you the last value.

that if the running balance goes up and down like a Yo-Yo, it won't find the last calculated cell? If my understanding is correct, that's not what I'm looking for.
I'm looking for the LAST calculated value in Column K. By the way, does 99 ^ 99 mean 99 to the power of 99?
I'll get back after I try this

Please Login or Register  to view this content.

but, as I said, I believe I tried this.
John

Is this helpful?

Please Login or Register  to view this content.

From there you can insert MyVal on your new sheet anywhere you want.

To clear up the question regards LOOKUP.

LOOKUP assumes (always) that the lookup_vector is sorted in ascending order it thus assumes (always) that the last value found (of same data type to criteria) is the biggest value... if that value is less than the criteria that will be the value returned. Thus

=LOOKUP(10,{2,#N/A,"",8,3,"a"}) --> returns 3

Values in the lookup_vector not of the same data type as criteria (number) are ignored and this includes error values (which is what makes LOOKUP even more useful than most) ... LOOKUP assumes 3 to be the biggest value - given < 10 it returns it as result.

It is obvious then that when looking for "last value" the criteria must exceed all possible values in the lookup_vector so as to avoid spurious results...often people will use 9.99999999999999E+307

So if your formulae return non-numerics when not in use the LOOKUP will return the last active balance.

Hi rwgrietveld
This code

Please Login or Register  to view this content.

does indeed return the last cell in K but not the last "Calculated cell. It returns K2000. Not what I'm looking for.

Hi JBeaucaire
The code you posted returns a type mismatch at this line

Please Login or Register  to view this content.

I'll see if I can figure out why. If you have an idea, it would be extremely helpful.

Hi DonkeyOte
I gotta figure out what you're saying 'cause I really don't understand. Are you saying if I have "non-numerics" anywhere in range "K:K" (which I do in headers) that it will always return the last active balance (meaning the last calculated formula)?
Further, are you saying that if my criteria were a gazillion dollars, it would ALWAYS find my last calculated value (assuming it doesn't exceed a gazillion dollars, of course)?
And further, are you suggesting that, were I to substitute

9.99999999999999E+307

for

Lookup(99 ^ 99,

it'll always return the last calculated value?
John

My macro uses the SPECIAL CELLS function to make a range out of all the cells with NUMBERS in them as a result of formulas. The line of code you noted should represent the last value in the array.

Post up a sample wb with it NOT working since it works in my test for any length of data in column K.

Ahhhhh DonkeyOte
I re-read the last line of your response

So if your formulae return non-numerics when not in use the LOOKUP will return the last active balance.]

The way I interpret this line is, if I have unused formulae, the code will ALWAYS return the last used formula. Is this correct?
John

DO is on a union-negotiated break.

It will return the result of the last formula in the (vector) range that returns a number, or the last literal number, ignoring text (including null strings returned by formulas), errors, and logical values.

So, Shg, is that yes?
John

I can't see your workbook.

You tried it, and ...?

Hi Shg
Have I tried what exactly? I have a procedure that works and I don't understand why (which is the crux of my question). DonkeyOte offered some comments on "LookUp" on which I asked some questions. What I'm asking is if I interpreted his comments correctly. What is it that was suggested I try? John

The attached wb incorporates JB's code. I'm trying to find the value of cell K497 so I can use it in a newly created workbook. I hope this upload works as it's part of a much larger workbook. I've tested it and it seems to do OK.
Thanks for your help.
John

The formula you have in K7 is killing the Set Rng command and I don't know why. So adjust it like so and it works fine:

Please Login or Register  to view this content.

Originally Posted by jaslake

Hi DonkeyOte
I gotta figure out what you're saying 'cause I really don't understand. Are you saying if I have "non-numerics" anywhere in range "K:K" (which I do in headers) that it will always return the last active balance (meaning the last calculated formula)?

shg has already answered most of this in truth but ... the LOOKUP will return the last numeric value in the range specified. Headers and non-numeric values will be ignored - this will include formulae nulls etc... (text).

Originally Posted by jaslake

Further, are you saying that if my criteria were a gazillion dollars, it would ALWAYS find my last calculated value (assuming it doesn't exceed a gazillion dollars, of course)?

Yes. LOOKUP assumes your balances are in ascending order and on that basis assumes last number found to be the biggest...thus as long as that value is less than the criteria value it will be returned.

Originally Posted by jaslake

And further, are you suggesting that, were I to substitute

9.99999999999999E+307

for

99 ^ 99,

If you're worried the criteria value of 99^99 is insufficient to capture your potential balance then I'm unsure as to why you don't have "people" to do this type of work for you
(assuming of course you don't live in Zimbabwe or some other country suffering absurd inflation etc...)

In terms of understanding how we can retrieve "last values" etc see Bob Phillips' white paper: http://www.xldynamic.com/source/xld.LastValue.html

Hi JBeaucaire
The code works well, thanks. I did some research on special cells and changed this line so it's more intuitive (to me).

Please Login or Register  to view this content.

Again, thanks for your help.

Hi DonkeyOte
Nah, I'm not worried about this

If you're worried the criteria value of 99^99 is insufficient to capture your potential balance

but it would be interesting to have those kind of problems.
I've looked at the white paper you referenced; I'll study it later. I appreciate the time you took to educate me. I enjoy logic and math problems but some of this stuff just gets my mind twisted. I often get thinking in a certain direction and, if it's not the right direction, I sometimes find it difficult to reverse direction. One of my many shortcomings.

Thanks good people. John

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)