Can you edit this to make it loop?

[guy6]

Hi everyone,

I would like to make the following code into a loop, but I do not understand how to do this - I'm hoping somebody can edit it to make it check for the numbers 1 to 100! Thanks in advance.

Sub FindAll()

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, Rng As Range
Dim myRange As Range, LastCell As Range

  fnd = "UNSURE OF WHAT TO PUT HERE"

Set myRange = Sheets("Sheet1").Range("A1:A100")
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If

Set Rng = FoundCell

  Do Until FoundCell Is Nothing
      Set FoundCell = myRange.FindNext(after:=FoundCell)
      Set Rng = Union(Rng, FoundCell)
      If FoundCell.Address = FirstFound Then Exit Do
      
  Loop

    Rng.EntireRow.Select

Exit Sub

NothingFound:
  MsgBox "No values were found in this worksheet"

End Sub

 

[NoSparks]

Not sure I understand what you're asking, maybe this ?

Sub findAll()
    Dim fnd As String, FoundCell As Range
    Dim myRange As Range, LastCell As Range, i As Long
    
Set myRange = Sheets("Sheet1").Range("A1:A100")
Set LastCell = Sheets("Sheet1").Range("A100")   'myrange already determined this

For i = 1 To 100
    Set FoundCell = myRange.Find(What:=i, After:=LastCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                               
    If Not FoundCell Is Nothing Then
        'it was found so you can
        'do what ever you want done here
        'before checking for the next number
    Else
        MsgBox i & "  was not found"
    End If
Next i

MsgBox "Finished checking"

End Sub

Have a look at the Range.Find and Range.FindNext methods if you're looking for more than one occurrence of something in the range.

 

[jasonb75]

If you're doing the same thing with every cell then you might find it more efficient to use something like

Sub FindAll()

Dim myRange As Range, FoundCell As Range

Set myRange = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Cells(Rows.Count,1).end(xlup).row)

For Each FoundCell In myRange
    Select Case FoundCell.Value
        Case 1 to 100
            ' do stuff with FoundCell
    End Select
Next FoundCell
End Sub

** Note:- Syntax not checked or tested, this is just a theory that you might be able to utilise **

 

[Fluff]

Depending on what you want to do, maybe

Sub guy6()
   Range("A1:A100").AutoFilter 1, ">=1", xlAnd, "<=100"
End Sub

 

[guy6]

Thank you all for getting back to me so quickly! It looks like NoSparks answer is the closest to what I am after, but will require some editing.

I should probably explain what I am trying to do, so you better understand the situation.

I have a table with autofilters on each column and each row is indexed according to their corresponding individual (if an individual's name appears in 5 different rows, his index number will be on all five rows). I am filtering this table of 100+ results down to a smaller amount, say 8-10. I would like the macro to search through the visible cells/filter results (the 8-10 results, not the whole of column A), find all of the cells that it is looking for (e.g. when it searches for 1, it selects all of the rows which are indexed to the number 1), and then I can execute a macro to send an email to this person. It will then loop round and find and select all of the results for 2 and send an email to that individual, etc etc.

The end result will be a macro that automatically searches for and creates an email for each individual that is in the search result (if column A is showing 1,1,1,5,5,14,14,14,14 then three emails will be created).

I am okay with the email macro, it is just the loop to find and select the right cells which I am struggling with!

 

[NoSparks]

Ok, the way I interpret things now is that you want a macro to run after you have filtered the data to give the unique values of column A.
Two For loops. First to populate a dictionary from the filtered results, and a second to loop through and display the results in the dictionary.

Sub Unique_Visibles_From_Column()
    Dim ws As Worksheet, lr As Long, i As Long
    Dim rng As Range, cel As Range, dic As Object
    
    Set ws = Sheets("Sheet1")
    Set dic = CreateObject("Scripting.Dictionary")
    
    With ws
        'get bottom of column A
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        'set range to work on
        Set rng = .Range("A2:A" & lr)
        'one cell at a time
        For Each cel In rng
            ' hidden or not ?
            If Not cel.EntireRow.Hidden Then
                'add to dictionary
                dic(CStr(cel.Value)) = 1
            End If
        Next cel
    End With
                
   'now have uniques of column A in dictionary
   'deal with them individually
    For i = 0 To dic.Count - 1
    ' do what you want here
        ' just to show which one you're currently dealing with
        '
        MsgBox "The one on this loop is  " & dic.Keys()(i)
        '
        '
    Next i
End Sub

Hope that helps.

 

[NoSparks]

As with everything Excel there is always more than one way to do everything.

I suspect somebody will happen by and provide a formula that will produce a list of Unique distinct values from the filtered results in column A.
And you could just copy the column A filtered results to some location and use Excels built in remove duplicates function (from the DATA ribbon) to leave you with what you're wanting.
In any case you'll likely need to loop through the resulting list for your e mailings.

 

[guy6]

That's great NoSparks, thank you so much - it is identifying the numbers shown in column A results and giving me individual msg boxes which validates it works!

Could you possibly help me with the last step of my macro? I would like the following 'do what you want here section:

   'deal with them individually
    For i = 0 To dic.Count - 1
    ' do what you want here

To filter column A by i, so it only shows the results that match the result it is searching for. I can then execute my email macro and reset the filter before it goes onto the next i.

I tried to use Google to help me with setting the autofilter criteria with a variable, but couldn't get any to work. This is the final step for the full macro I need, so hopefully this is possible

Thanks again!!

 

[NoSparks]

Give this a try

   'deal with them individually
    For i = 0 To dic.Count - 1
    ' do what you want here
        With ws
            'remove existing filters
            If .AutoFilterMode Then .AutoFilterMode = False
            'filter individually
            .Range("A:A").AutoFilter Field:=1, Criteria1:=dic.Keys()(i)
            '
            ' do your email stuff here
            '
        End With
    Next i

 

[guy6]

Amazing! Due to the rest of the macro, I didn't need the following line:

            'remove existing filters
            If .AutoFilterMode Then .AutoFilterMode = False

But it works perfectly! Thank you so so much for your help NoSparks, I really appreciate it.