Blank cells & formula cells
 

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307, r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Thanks for responding. Your solution is good for finding the value of the
last cell that has a value, but I am looking to find that cell, not its
value. Thanks again. Otto
"Gary''s Student" wrote in message
...
Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307, r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Switch to Match from lookup...

Sub test()
MsgBox (Application.WorksheetFunction.Match(9.999E+307, Columns("A")))
End Sub
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Thanks for responding. Your solution is good for finding the value of the
last cell that has a value, but I am looking to find that cell, not its
value. Thanks again. Otto
"Gary''s Student" wrote in message
...
Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307, r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Jim
Thanks for that, but I'm not looking for the last cell that has a
number. I'm looking for the last non-blank cell in a range in which all
cells have a formula. In my case, all non-blank cells have a name. Thanks
again. Otto
"Jim Thomlinson" wrote in message
...
Switch to Match from lookup...

Sub test()
MsgBox (Application.WorksheetFunction.Match(9.999E+307, Columns("A")))
End Sub
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Thanks for responding. Your solution is good for finding the value of
the
last cell that has a value, but I am looking to find that cell, not its
value. Thanks again. Otto
"Gary''s Student" wrote in
message
...
Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307, r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Give this a look... there is some code at the end that you might be able to
use...

http://www.xldynamic.com/source/xld.LastValue.html#vba
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Jim
Thanks for that, but I'm not looking for the last cell that has a
number. I'm looking for the last non-blank cell in a range in which all
cells have a formula. In my case, all non-blank cells have a name. Thanks
again. Otto
"Jim Thomlinson" wrote in message
...
Switch to Match from lookup...

Sub test()
MsgBox (Application.WorksheetFunction.Match(9.999E+307, Columns("A")))
End Sub
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Thanks for responding. Your solution is good for finding the value of
the
last cell that has a value, but I am looking to find that cell, not its
value. Thanks again. Otto
"Gary''s Student" wrote in
message
...
Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307, r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto

Blank cells & formula cells
 

Jim
Playing around with this problem I came up with the following. From
what I can see, this is doing what I want. Does anything about this jump
out at you as a problem? All the cells in the range contain a formula but
only some result in a value. I appreciate your help and your time. Otto
Sub TestFind()
Dim TheRng As Range
Dim TheCell As Range
Set TheRng = Range("W6:W25")
Set TheCell = TheRng.Find(What:="*", After:=TheRng(1),
LookIn:=xlValues, SearchDirection:=xlPrevious)
MsgBox TheCell.Address(0, 0)
End Sub
"Jim Thomlinson" wrote in message
...
Give this a look... there is some code at the end that you might be able
to
use...

http://www.xldynamic.com/source/xld.LastValue.html#vba
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Jim
Thanks for that, but I'm not looking for the last cell that has a
number. I'm looking for the last non-blank cell in a range in which all
cells have a formula. In my case, all non-blank cells have a name.
Thanks
again. Otto
"Jim Thomlinson" wrote in
message
...
Switch to Match from lookup...

Sub test()
MsgBox (Application.WorksheetFunction.Match(9.999E+307, Columns("A")))
End Sub
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Thanks for responding. Your solution is good for finding the value of
the
last cell that has a value, but I am looking to find that cell, not
its
value. Thanks again. Otto
"Gary''s Student" wrote in
message
...
Very similar to the expression in the worksheet:

Sub ordinate()
Set r = Range("A1:A20")
MsgBox (Application.WorksheetFunction.Lookup(9.9999999999 9999E+307,
r))
End Sub

will output the value of the last non-blank numeric cell in A1 thru
A20
--
Gary''s Student - gsnu200764


"Otto Moehrbach" wrote:

Excel XP & Win XP
Say I have a column of 20 contiguous cells.
All 20 cells have formulas.
The top X number of cells have values, by formulas.
The rest of the cells have blanks, by formulas.
Short of looping up the column looking for (NOT ""), is there a
better/quicker way of finding the last value cell?
Thanks for your time. Otto