VBA loop until last instance of Find function

[Ada01]

I have the following find function followed by a cell offset and formula to grab the correct cell reference (this stems from Text to Cols on an old school text file from and old school system)

Sub Find_Term()'
' find term and concatenate necessary cells


    Columns("A:A").Select
    
' to be looped until the last instance of STPL


    Selection.Find(What:="Location STPL", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5]&RC[6])"


End Sub

I would like to repeat the find and concatenate functions until the last time the "location stpl" is found and then stop.

Thanks

 

[John_w]

You need a Find and FindNext loop. The VBA help for either method has example code.

 

[Ada01]

John

I have tried that option, and had odd results. As near as I can tell, the results of the below version of the code simply finds and acknowledges the existence of the find, changes all of them to TRUE and then starts concatenating each individual row starting on row one.

Sub TEMP_Search_STPL()

Dim my_findRng As Range
Dim my_find As Range


Set my_findRng = Range("A:A")




With my_findRng
    Set my_find = .Find(What:="Location STPL")
    If Not my_find Is Nothing Then
        firstAddress = my_find.Address


        Do
            ActiveCell.Offset(1, 0).Select
            my_find.Value = ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5]&RC[6])"
            Set my_find = .FindNext(my_find)
        Loop While Not my_find Is Nothing And my_find.Address <> firstAddress
    End If
    
End With
    


End Sub

A little guidance on how to correct would be much appreciated.

Thanks

 

[Fluff]

Hello Ada01
This is an alternative way of doing it

Sub Find_Term() '
' find term and concatenate necessary cells

    Dim Cntr As Long
    Dim i As Long

' to be looped until the last instance of STPL

    Cntr = WorksheetFunction.CountIf(Columns(1), "Location STPL")

    For i = 1 To Cntr
        Columns(1).Find(What:="Location STPL", After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5]&RC[6])"
    Next i

End Sub

HTH

 

[iliauk]

Perhaps:

Dim rng As Range
Dim LastCell As Range
Dim Foundcell As Range
Dim FirstAddr As String
Set rng = Range("A:A")


With rng
    Set LastCell = .Cells(.Cells.Count)
    Set Foundcell = .Find(What:="Location STPL", After:=LastCell)
    If Not Foundcell Is Nothing Then
        FirstAddr = Foundcell.Address
    End If
    Do Until Foundcell Is Nothing
        Foundcell.Offset(1, 0).FormulaR1C1 = "=CONCATENATE(RC[5]&RC[6])"
        Set Foundcell = .FindNext(After:=Foundcell)
        If Foundcell.Address = FirstAddr Then Exit Do
    Loop
End With