VBA function to find last nonzero value in a column






Re: VBA function to find last nonzero value in a column
Try this:
CodePublic Function MYLOOKUP(ByVal rng As Range) As Long Application.Volatile If rng.Columns.Count > 1 Then MYLOOKUP = 99 Exit Function End If MYLOOKUP = Evaluate("LOOKUP(2,1/(" & rng.Address & ">0)," & rng.Address & ")") End Function
You would then use it in your worksheet in the format
=MYLOOKUP(A1:A20) 

Re: VBA function to find last nonzero value in a column
Thanks, I never knew you could use Evaluate.
Could you also explain what Application.Volatile does?
thanks again! 
Re: VBA function to find last nonzero value in a column
Try this
Code
Display MoreFunction LastNonZero(Rng As Range) Dim LastRow As Long, lCol As Long, X As Long, I As Long, Cell As Range For Each Cell In Rng.Columns If Cells(Rows.Count, Cell.Column).End(xlUp).Row > X Then X = Cells(Rows.Count, Cell.Column).End(xlUp).Row: lCol = Cell.Column Next Cell LastRow = Rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For I = LastRow To 1 Step 1 If Cells(I, lCol) > 0 Then Exit For Next I LastNonZero = Cells(I, lCol).Address End Function

Re: VBA function to find last nonzero value in a column
Hi,
It might not make any difference to your application, but this is Microsoft's explanation:
Quote
A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change.
If any of the values in your formula's range change, then the formula will recalculate without being volatile, so you might not need that. I would suggest you put the custom function alongside the standard LOOKUP (without the volatile statement) and test to make sure that they both update simultaneously.There are a number of Excel's standard functions that are volatile, probably the main two that people might use and be unaware that they are volatile are OFFSET and INDIRECT. If you use lots of these formulas in your spreadsheets, be aware that they may update unexpectedly, and slow down your spreadsheet.

Re: VBA function to find last nonzero value in a column
Quote from Batman;754459Hi,
It might not make any difference to your application, but this is Microsoft's explanation:
If any of the values in your formula's range change, then the formula will recalculate without being volatile, so you might not need that. I would suggest you put the custom function alongside the standard LOOKUP (without the volatile statement) and test to make sure that they both update simultaneously.
There are a number of Excel's standard functions that are volatile, probably the main two that people might use and be unaware that they are volatile are OFFSET and INDIRECT. If you use lots of these formulas in your spreadsheets, be aware that they may update unexpectedly, and slow down your spreadsheet.
Hi,
Thanks for the explanation.
I did some test, it appears that whenever I use a macro to get data from external sources.
The custom function will go to #VALUE error, while the regular LOOKUP is fine.
To get out of that #VALUE error I have to change the value of any cell.Is there any way around this so I would not have to correct the error every time?

Re: VBA function to find last nonzero value in a column
Quote from BaraaKhalil;754427Try this
Code
Display MoreFunction LastNonZero(Rng As Range) Dim LastRow As Long, lCol As Long, X As Long, I As Long, Cell As Range For Each Cell In Rng.Columns If Cells(Rows.Count, Cell.Column).End(xlUp).Row > X Then X = Cells(Rows.Count, Cell.Column).End(xlUp).Row: lCol = Cell.Column Next Cell LastRow = Rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For I = LastRow To 1 Step 1 If Cells(I, lCol) > 0 Then Exit For Next I LastNonZero = Cells(I, lCol).Address End Function
Hi,
This works as well.
However when I did some test, when I use a macro to get data from external sources, it returns the last nonzero value of the range of the ACTIVE sheet, which is not the sheet that has the data and the function.
Could you change the code a bit so that it specify which sheet is used for calculation.
Thanks a lot!! 

Re: VBA function to find last nonzero value in a column
Does the macro affect the values in the range you have used in the formula? Do you still have Application.Volatile in the function? Does your macro turn off automatic calculation and/or recalculate?
Or is it the case that you want a custom function that you use from within a macro, and not a function that you use from within a worksheet (the latter was my understanding of the requirement)?
Making changes manually to my sample workbook doesn't affect the formula, so I'm not sure why running a macro would.
We might need to see a sample of your workbook, including the macro that affects the formula.

Re: VBA function to find last nonzero value in a column
Try this modification (Berlan helped me with that point in ExcelForum)
http://www.excelforum.com/excelprogramm…anysheet.html
Code
Display MoreFunction LastNonZero(Rng As Range) '=LastNonZero(Sheet1!A1:B100) Dim LastRow As Long, lCol As Long, X As Long, I As Long, Cell As Range For Each Cell In Rng.Columns If Rng.Parent.Cells(Rows.Count, Cell.Column).End(xlUp).Row > X Then X = Rng.Parent.Cells(Rows.Count, Cell.Column).End(xlUp).Row: lCol = Cell.Column Next Cell LastRow = Rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For I = LastRow To 1 Step 1 If Rng.Parent.Cells(I, lCol) > 0 Then Exit For Next I LastNonZero = Rng.Parent.Cells(I, lCol).Address End Function

Re: VBA function to find last nonzero value in a column
Batman,
The macro is periodically getting data and update them in the range that the formula uses.
I tested both with and without Application.Volatile, and it always gets #VALUE error.
I tested manually changing the values in the range and it didn't get the error.
the function is to be used from within the sheet, the macro has nothing to do with it, it simply gets data and update the range.Baraakhalil,
Yes, it works great now. I'll do some more test to see if there will be any problems.
And thank you both! You are both being very helpful!!
Best,
Jerry 
Re: VBA function to find last nonzero value in a column
Hi Jerry
you're welcome.
Glad I can help. Thanks for the feedback
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!