Do loop with find

P

paularo

I need to have a "do" loop that goes until it can no longer find the item
searched for. How do I word that? What's comes after "do until" on the
first line of the statement? I already have the rest written!

Thanks.
 
M

Matthew Herbert

I need to have a "do" loop that goes until it can no longer find the item
searched for.  How do I word that?   What's comes after "do until" onthe
first line of the statement?  I already have the rest written!

Thanks.
Click to expand...

Paularo,

Can you post your syntax? You will likely need to create an anchor
for the first found range and when the Next find takes place you can
test the Next address against the anchor address to determine whether
you are at the start again (i.e. the first found range).

Best,

Matthew Herbert
 
J

Jim Thomlinson

Generally spaeaking the loop is this...
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range
Dim strFirst As String

On Error GoTo ErrorHandler

Set rngToSearch = sheets("sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This and That", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=True)
If rngFound Is Nothing Then
msgbox "Not Found"
Else
Set rngFoundAll = rngFound
strFirst = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundAll.entirerow.select
End If
 
P

paularo

Here's my "body of work" on this one. I just want it to keep going until it
no longer finds whatever has been assigned to the string "category". From
what I've seen so far, I may be going in the wrong direction here, but
simplicity is preferred whenever possible since most of the folks I work with
will have no idea!

Any help to keep it simple would be appreciated!

Paula

------------
Sub categoryselect()
category = InputBox("enter category here", "Category selection")
Range("B2:b500").Activate
do until ????
Selection.Find(What:=category, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Hidden = False
Cells.FindNext(After:=ActiveCell).Activate
Loop

End Sub
 
J

Jim Thomlinson

This will do it. Finds require a bit of work to ensure that they do not blow
up if nothing is found.

Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range
Dim strFirst As String
Dim strCategory as string

On Error GoTo ErrorHandler
strCategory = InputBox("enter category here", "Category selection")

Set rngToSearch = activesheet.range('B2:B500")
Set rngFound = rngToSearch.Find(What:=strcategory, _
LookAt:=xlWhole, _
LookIn:=xlformulas, _
MatchCase:=false)
If rngFound Is Nothing Then
msgbox "Could not find " & strCategory
Else
Set rngFoundAll = rngFound
strFirst = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundAll.entirerow.hidden = false
End If
 
R

Rick Rothstein

To the best of my knowledge, the Find Method will not search in hidden
cells. See if this macro does what you want instead...

Sub UnhideSpecifiedRows()
Dim X As Long, LR As Long, Category As String
Category = InputBox("Enter category here", "Category selection")
If Len(Category) = 0 Then Exit Sub
LR = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For X = 1 To LR
If Rows(X).Hidden Then
If StrComp(Cells(X, "H").Value, Category, vbTextCompare) = 0 Then
Rows(X).Hidden = False
End If
End If
Next
End Sub
 
M

Matthew Herbert

To the best of my knowledge, the Find Method will not search in hidden
cells. See if this macro does what you want instead...

Sub UnhideSpecifiedRows()
  Dim X As Long, LR As Long, Category As String
  Category = InputBox("Enter category here", "Category selection")
  If Len(Category) = 0 Then Exit Sub
  LR = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
  For X = 1 To LR
    If Rows(X).Hidden Then
      If StrComp(Cells(X, "H").Value, Category, vbTextCompare) = 0 Then
         Rows(X).Hidden = False
      End If
    End If
  Next
End Sub

--
Rick (MVP - Excel)









- Show quoted text -
Click to expand...

Paularo,

As a follow up to Rick, Find will find items in hidden cells under
certain criteria. If your LookIn parameter is set to xlFormulas then
Find will locate the hidden item if the item is a value. If the item
is, say, linked to another worksheet, then xlFormulas will not find
the item in the hidden cells.

Best,

Matt
 
Top