Function to find last value in a column

[Andyjim]

Hi-

I submitted this post without entering anything on the subject line. So I
am re-entering this. I need to create a function that can be placed in a
cell that will display the last number in another column. Below are some
attempts, but no success. Thanks for your help!

Andy


Function LastValue(lastno As Variant)

Dim sysexpcol As Range
Set sysexpcol = Range(ad16, ad56)


Set lastno = sysexpcol(Cells.Value.xlDown)



End Function

Function lastvalueincolumn(foundcell As Variant)

With Worksheets("analysis").Range("ad:ad")
Set foundcell = .Cells.Find.Value.xlDown
End With
End Function

 

[JLGWhiz]

The best method for finding the last cell with data in a column on the active
sheet is:

lastRow = Cells(Rows.Count, "AD").End(xlUp).Row

You can substitute the column number without quotes where the "AD" appears.
It works either way. This does a bottoms up search of the column to find the
absolute last cell since the xlDown would stop if there is a blank cell
between the starting cell and the last cell. To set a variable to the range
for that cell:

myVar = Range("AD" & lastRow)

 

[FSt1]

hi
you don't really need a function unless you just gotta have one
try something like this.

=OFFSET(A1,COUNTIF(A1:A500,">0")-1,0)

adjust cell references to suit your data.

regards
FSt1

 

[Gord Dibben]

Why not use the functions Excel provides?

=LOOKUP(99^99,A:A) will fetch the last numeric in column A

=LOOKUP(REPT("z",255),A:A) fetch last text value in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) fetch wha is in last cell of column A


Gord Dibben MS Excel MVP

 

[Andyjim]

Thanks for the help.

I not very experienced at creating functions as you can see.
I want to place this function if Cell d5 and have it display the value of
the last cell in column AD.

Function LastValue(lastno As Variant)


myvar = Range("AD" & lastrow)


Set lastno = Cells(Rows.Count, "AD").End(xlUp).Row
lastno = Range.Cells.Value



End Function

What am I doing wrong?

Thanks again for your help.

Andy

 

[Rick Rothstein \(MVP - VB\)]

I not very experienced at creating functions as you can see.

I want to place this function if Cell d5 and have it display the value of
the last cell in column AD.

If that is all you want to do, just use this formula in D5...

=LOOKUP(2,1/(AD1:AD65535<>""),AD1:AD65535)

Rick

 

[Andyjim]

Thanks to all for your answers. I'm sure they will work!

 

[swastikhotmail]

Simply superb.

Swastik

Why not use the functions Excel provides?

=LOOKUP(99^99,A:A) will fetch the last numeric in column A

=LOOKUP(REPT("z",255),A:A) fetch last text value in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) fetch wha is in last cell of column A


Gord Dibben MS Excel MVP

 

[sybolt hoitinga]

Gord,
I've been trying your solution to fetch what's in the last cell in column A and it works absolutely great, but you got me puzzled here.

What is the 2 in =LOOKUP(2, ?
What's the effect of the division 1/(A:A<>"")

Please enlighten me.

cheers,

Sybolt

 

[Ben McClave]

Sybolt,

As I understand it, the division piece will return an array of values that includes one of two results:

1/TRUE = 1/1 = 1 and
1/FALSE = 1/0 = #DIV/0!

So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:

(1,1,1,#DIV/0!,#DIV/0!)

Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).

Ben

 

[abhijeetgaddam]

Sybolt,



As I understand it, the division piece will return an array of values that includes one of two results:



1/TRUE = 1/1 = 1 and

1/FALSE = 1/0 = #DIV/0!



So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:



(1,1,1,#DIV/0!,#DIV/0!)



Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).



Ben

Sybolt,



As I understand it, the division piece will return an array of values that includes one of two results:



1/TRUE = 1/1 = 1 and

1/FALSE = 1/0 = #DIV/0!



So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:



(1,1,1,#DIV/0!,#DIV/0!)



Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).



Ben

check this link you will get ans for sure.

http://www.xl-central.com/lookup-last-instance.html