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

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

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

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

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