Excel 2003 - iterating column A and finding same words

[DougP]

I have a Excel document that repeating data such as the word "Location:" in cells all the way down.
I want to find that cell and get its contents then move to the next occurance get its contents and so on.
here is some code

Sub GetSampleNumbers()
    Dim r As Range
    Set r = Range("A1:A900").Find("Location:")
    If Not r Is Nothing Then r.Select
    
End Sub

TIA

DougP, MCP, A+

I love this site and all you folks that helped me over the years!

 

[PHV]

Directly inspired from the VBA help:
With Worksheets(1).Range("a1:a900")
Set c = .Find("Location:")
If Not c Is Nothing Then
firstAddress = c.Address
Do
'some stuff with actual c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

I also need to find Asbestos Type and put it in a cell next to another cell. But the code I have is not working, it is substituting my previous .find of PE- with Asbestos Type: and getting stuck in a endless loop.
The variable TheSample is changing from PE- to Asbestos Type: for some reason.
Can some one help me with this?

    Dim SamplePrefix As String
    Dim counter As Integer
    
    SamplePrefix = InputBox("Please Key in Sample Number Prefix", "Prefix Number", "PE")
    If SamplePrefix = "" Then
        Exit Sub
    Else
        SamplePrefix = SamplePrefix & "-"
    End If
    
    With Worksheets(1).Range("a1:a900")
    ' get job number
    Set JobNum = .Find("RE ")
    Sheets("Results").Range("D2") = Trim(Mid(JobNum, 3, Len(JobNum) - 3))
    
        counter = 7
        Set TheSample = .Find(SamplePrefix)
        If Not TheSample Is Nothing Then
        firstSample = TheSample.Address
        Do
            If InStr(1, TheSample, SamplePrefix) <> 1 Then
                'don't use it
            Else
                counter = counter + 1
                If Len(TheSample) > 10 Then
                    Sample = Trim(Left(TheSample, InStr(1, TheSample, " ")))
                    'check for word Yes or No
                    If InStr(1, TheSample, "Yes") Then
                        Worksheets("Results").Cells(counter, 8).Value = "Y"
                        ' look for Asbestos Types:
                        'Set AsbestosType = .Find("Asbestos Types: ")
                        'Worksheets("Results").Cells(counter, 10).Value = Trim(Right(AsbestosType, Len(AsbestosType) - 16))
                    Else
                        Worksheets("Results").Cells(counter, 8).Value = "N"
                    End If
                Else
                    Sample = Trim(TheSample)
                End If
                Worksheets("Results").Cells(counter, 3).Value = Sample
                Debug.Print Sample
            End If
            Set TheSample = .FindNext(TheSample)
            Loop While Not TheSample Is Nothing And TheSample.Address <> firstSample
        End If

DougP, MCP, A+

I love this site and all you folks that helped me over the years!