| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula with blank cells | Excel Discussion (Misc queries) | |||
| Formula To Keep Certain Cells Blank | Excel Worksheet Functions | |||
| Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
| Formula Help: Add cells with certain text + cells that are blank | Excel Worksheet Functions | |||
| Need blank formula cells rather than 0's | Excel Worksheet Functions | |||
Linear Mode
