VBA Find Start and End of a list within a range

[Ziggy12]

Hi
I've got sorted list with a range name. All items in the list are grouped alphbetically. The same item can appear once or many times. I've used the find function to find the address of the first instance of the item but need to find the last as well. The Msgbox below returns $A$4. The last instance of Ziggy is in $A$10. I want the message box to return $A$4:$A$10

Sub FindValues()

Dim c As Range
Dim firstAddress As String

With Sheets("BigList").Range("Details")
Set c = Cells.Find(What:="Ziggy", LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub

Actuall what I really want to do is populate a combobox with ComboBox1.List = Sheets(2).Range("A2:A10").Value

 

[rlv01]

Perhaps something like this.

Sub FindValues()
    Dim c As Range
    Dim FirstAddress As String, LastAddress As String
    
    With Sheets("BigList").Range("Details")
        Set c = Cells.Find(What:="Ziggy", LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do While Not c Is Nothing
                Set c = Cells.Find(What:="Ziggy", After:=c, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext)
                If c.Address = FirstAddress Then
                    Set c = Nothing
                    Exit Do
                Else
                    LastAddress = c.Address
                End If
            Loop
            If FirstAddress <> "" And LastAddress <> "" Then
                MsgBox "Range: " & FirstAddress & ":" & LastAddress
            Else
                MsgBox "Range not found"
            End If
        End If
    End With
End Sub

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Ziggy12]

Thanks Riv01

I think I've cracked it to
Sub MultipleSearch()

' Get name to search
Dim name As String: name = "Ziggy"

' Get search range
Dim rgSearch As Range
Set rgSearch = Sheets("Sheets List").Range("BigList")

Dim cell As Range
Set cell = rgSearch.Find(name)

' If not found then exit
If cell Is Nothing Then
MsgBox "Not found"
Exit Sub
End If

' Store first cell address
Dim firstCellAddress As String
firstCellAddress = cell.Address

' Find all cells containing Elli
Do
lastcelladdress = cell.Address
Set cell = rgSearch.FindNext(cell)
Loop Until firstCellAddress = cell.Address
MsgBox firstCellAddress & ":" & lastcelladdress

End Sub

 

[rlv01]

Sure, that will do it as well. You still need to learn to use code tags though.

 

[Ziggy12]

Code tags?

 

[Ziggy12]

Oh found what you mean thanks.