| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news  475ABD3-B72F-4D65-94EE-(E-Mail Removed)...> 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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in >> message >> news:75114B5F-2358-4EE5-8003-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in >> >> message >> >> news:0ED4017D-D033-4467-8E7E-(E-Mail Removed)... >> >> > Very similar to the expression in the worksheet: >> >> > >> >> > Sub ordinate() >> >> > Set r = Range("A1:A20") >> >> > MsgBox (Application.WorksheetFunction.Lookup(9.99999999999999E+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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Re: avg formula and blank cells | AltaEgo | Microsoft Excel Discussion | 1 | 21st May 2009 09:46 AM |
| Blank cells in a formula | jtraas | Microsoft Excel Misc | 2 | 25th Feb 2009 07:06 PM |
| Formula Help: Add cells with certain text + cells that are blank | =?Utf-8?B?Tmljb2xlIEwu?= | Microsoft Excel Worksheet Functions | 3 | 27th Feb 2007 06:59 AM |
| VB- If first cell with formula is blank, all cells in column returns blank. | mnhesh | Microsoft Excel Misc | 2 | 12th May 2004 05:14 PM |
| How do I leave formula cells blank until corresponding cells are entered? | Dean | Microsoft Excel Worksheet Functions | 2 | 26th Sep 2003 08:25 PM |
