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.

On Aug 13, 1:05*pm, paularo <paul...@discussions.microsoft.com> wrote:
> 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.

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
--
HTH...

Jim Thomlinson


"paularo" wrote:

> 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.

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


"Matthew Herbert" wrote:

> On Aug 13, 1:05 pm, paularo <paul...@discussions.microsoft.com> wrote:
> > 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.
>
> 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
>

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

--
HTH...

Jim Thomlinson


"paularo" wrote:

> 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
>
>
> "Matthew Herbert" wrote:
>
> > On Aug 13, 1:05 pm, paularo <paul...@discussions.microsoft.com> wrote:
> > > 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.
> >
> > 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
> >

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)


"paularo" <(E-Mail Removed)> wrote in message
newsE67884E-AF73-4051-BD54-(E-Mail Removed)...
> 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
>
>
> "Matthew Herbert" wrote:
>
>> On Aug 13, 1:05 pm, paularo <paul...@discussions.microsoft.com> wrote:
>> > 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.
>>
>> 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
>>

On Aug 13, 2:54*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> 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)
>
> "paularo" <paul...@discussions.microsoft.com> wrote in message
>
> newsE67884E-AF73-4051-BD54-(E-Mail Removed)...
>
>
>
> > 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
>
> > "Matthew Herbert" wrote:
>
> >> On Aug 13, 1:05 pm, paularo <paul...@discussions.microsoft.com> wrote:
> >> > 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.
>
> >> 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- Hide quoted text -
>
> - Show quoted text -

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