Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
more find help please
i am looking in a range of cells for non-blank cells. the range has formulas
that may equate to blank, so they're blank, not empty.=IF(J24=0,"",J24) i was looking for blank cells and this routine worked, but i don't know how to end the loop. or, if there is a better way, please let me know With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) Do If rngfound Is Nothing Then Else MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) End If Next Loop While -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
more find help please
Dim strFirstAddress as String
With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) if not rngfound is nothing then strFirstAddress = rngfound.address Do MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) Loop Until rngfound.address = strfirstaddress End if -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i am looking in a range of cells for non-blank cells. the range has formulas that may equate to blank, so they're blank, not empty.=IF(J24=0,"",J24) i was looking for blank cells and this routine worked, but i don't know how to end the loop. or, if there is a better way, please let me know With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) Do If rngfound Is Nothing Then Else MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) End If Next Loop While -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
more find help please
thanks jim.
i found this: strFirstAddress = rngfound.address and used it but still couldn't get it to work. i needed this: Loop Until rngfound.address = strfirstaddress now it works fine. thanks again -- Gary "Jim Thomlinson" wrote in message ... Dim strFirstAddress as String With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) if not rngfound is nothing then strFirstAddress = rngfound.address Do MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) Loop Until rngfound.address = strfirstaddress End if -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i am looking in a range of cells for non-blank cells. the range has formulas that may equate to blank, so they're blank, not empty.=IF(J24=0,"",J24) i was looking for blank cells and this routine worked, but i don't know how to end the loop. or, if there is a better way, please let me know With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) Do If rngfound Is Nothing Then Else MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) End If Next Loop While -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
more find help please
jim:
i still have the same problem. i find all of the blank cells, but that's not what i want. i want all of the other cells. so the code below gives me all of the a addresses of the blank cells in the do loop, but i need all of the others. i thought maybe changing if not rngfound is nothing then to if rngfound is nothing then unless there is a better way to find values in the range that aren't blank thanks -- Gary "Jim Thomlinson" wrote in message ... Dim strFirstAddress as String With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) if not rngfound is nothing then strFirstAddress = rngfound.address Do MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) Loop Until rngfound.address = strfirstaddress End if -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i am looking in a range of cells for non-blank cells. the range has formulas that may equate to blank, so they're blank, not empty.=IF(J24=0,"",J24) i was looking for blank cells and this routine worked, but i don't know how to end the loop. or, if there is a better way, please let me know With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) Do If rngfound Is Nothing Then Else MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) End If Next Loop While -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
more find help please
i'm just going to use a for each cell statement and evaluate each cell
-- Gary "Jim Thomlinson" wrote in message ... Dim strFirstAddress as String With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) if not rngfound is nothing then strFirstAddress = rngfound.address Do MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) Loop Until rngfound.address = strfirstaddress End if -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i am looking in a range of cells for non-blank cells. the range has formulas that may equate to blank, so they're blank, not empty.=IF(J24=0,"",J24) i was looking for blank cells and this routine worked, but i don't know how to end the loop. or, if there is a better way, please let me know With ws.Range("AS23:AV" & lastrow) Set rngfound = .Find(What:="", LookIn:=xlValues) Do If rngfound Is Nothing Then Else MsgBox rngfound.Address ' code goes here Set rngfound = .FindNext(rngfound) End If Next Loop While -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |