Select data on row, depending on data 2 rows down

[unter]

Hi all,

Wonder if someone call help me with something.

Basically I have a whole load of data which I need to sort of "sanitize" so to speak.

The data looks like this (it can be in a text doc or put into a new sheet)

mac-address (omitted)
profile persons-name
domain LONDON
hostname whatever (omitted)
!
mac-address (omitted)
profile persons-name
domain DUBLIN
hostname whatever (omitted)

!
mac-address (omitted)
profile persons-name
domain BERLIN
hostname whatever (omitted)

!
mac-address (omitted)
profile persons-name
domain LONDON
hostname whatever (omitted)

What I need to do is, when LONDON is selected in a ComboBox, it will read down all the data and everytime LONDON is found, return the value of the mac-address and put all of them into a new column, with each mac on a separate row.

I'm sure this is something relatively simple, but cannot seem to get my head around how to construct the for loop for it, or whether a for loop is the right function to use or not.

Any help really appreciated.

Unter

 

[unter]

Sorry to bump this, but was hoping someone could help.

Regards,

Unter

 

[mumps]

Assuming your data is in column A, the mac-address will be put in column B:

Sub SelectData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, foundVal As Range, saddr As String
    Set foundVal = Range("A:A").Find("LONDON", LookIn:=xlValues, lookat:=xlPart)
    If Not foundVal Is Nothing Then
        saddr = foundVal.Address
        Do
            Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = foundVal.Offset(-2, 0)
            Set foundVal = Range("A:A").FindNext(foundVal)
        Loop While foundVal.Address <> saddr
        saddr = ""
    End If
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = True
End Sub