Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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) "Andyjim" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 "Andyjim" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 On Sun, 27 Jan 2008 12:01:00 -0800, Andyjim wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
Thanks to all for your answers. I'm sure they will work!
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
Simply superb.
Swastik On Monday, January 28, 2008 2:01:10 AM UTC+5:30, Gord Dibben wrote: 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 On Sun, 27 Jan 2008 12:01:00 -0800, Andyjim wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to find last value in a column
On Thursday, December 6, 2012 7:26:53 PM UTC+5:30, Ben McClave wrote:
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 On Thursday, December 6, 2012 7:26:53 PM UTC+5:30, Ben McClave wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to find last value in a column | Excel Programming | |||
trying to write function to find data in last row in 1 column | Excel Discussion (Misc queries) | |||
In a column of numbers I need a function to find the last entry | Excel Worksheet Functions | |||
Is there a function in VBA to find out if entire column is empty? | Excel Programming | |||
Function to Find Last Non-Null Entry in a Column | Excel Programming |