Do loop with find

  • Thread starter Thread starter paularo
  • Start date Start date
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.
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top