Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Next Row With No Value In It - But Not Next Row With No FormulaIn It
I've got this
nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Next Row With No Value In It - But Not Next Row With No FormulaIn It
It's always better to include all the parms for .find(). If you don't, then
you're at the mercy of the previous Find--either by the user or by your (or other's) code. And one of those "hidden" options is that you're looking through formulas. If you specify values, then you may see it work ok. Another problem with your snippet of code is if there isn't anything found. You won't be able to add 1 to the row number (since the cell wasn't found and it has no row!). I'd use something like: Option Explicit Sub testme() Dim NextRow As Long Dim FoundCell As Range With ActiveSheet Set FoundCell = .Cells.Find(what:="*", _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) If FoundCell Is Nothing Then NextRow = 1 'top row Else NextRow = FoundCell.Row + 1 End If .Cells(NextRow, 1).Select End With End Sub And the asterisk represents a wildcard--any set of characters. Cells(1) is the first cell (A1 in this example). robzrob wrote: I've got this nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Next Row With No Value In It - But Not Next Row With NoFormula In It
On Mar 5, 1:31*pm, Dave Peterson wrote:
It's always better to include all the parms for .find(). *If you don't, then you're at the mercy of the previous Find--either by the user or by your (or other's) code. And one of those "hidden" options is that you're looking through formulas.. *If you specify values, then you may see it work ok. Another problem with your snippet of code is if there isn't anything found. *You won't be able to add 1 to the row number (since the cell wasn't found and it has no row!). I'd use something like: Option Explicit Sub testme() * * Dim NextRow As Long * * Dim FoundCell As Range * * With ActiveSheet * * * * Set FoundCell = .Cells.Find(what:="*", _ * * * * * * * * * * * * * * After:=.Cells(1), _ * * * * * * * * * * * * * * LookIn:=xlValues, _ * * * * * * * * * * * * * * LookAt:=xlWhole, _ * * * * * * * * * * * * * * SearchOrder:=xlByRows, _ * * * * * * * * * * * * * * SearchDirection:=xlPrevious, _ * * * * * * * * * * * * * * MatchCase:=False) * * * * If FoundCell Is Nothing Then * * * * * * NextRow = 1 'top row * * * * Else * * * * * * NextRow = FoundCell.Row + 1 * * * * End If * * * * .Cells(NextRow, 1).Select * * End With End Sub And the asterisk represents a wildcard--any set of characters. * Cells(1) is the first cell (A1 in this example). * robzrob wrote: I've got this * *nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 * * Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. *Can I do this by amending what's in the what:="*" part of this code? -- Dave Peterson Thanks - I'll try that in my next workbook - I'm a bit wary of taking out sections of code and replacing them - don't want the whole thing to go haywire. But you've shown me how it works, so I know what to do, ie insert LookIn:=xlValues in my current code. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
I need help setting up a financial formulain in Excell | Excel Worksheet Functions | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming |