Help needed with Do Loop

[zombiemaster]

I'm trying to look at data and each time it finds the word "TYPE" in column G, it does some formatting and copy/paste stuff, then moves on to the next instance. I figured a DO LOOP would work best, and it works for what I need until it reaches the last "TYPE", then crashes. I'm not very familiar with the DO LOOP so I'm sure it's a simple fix. I want it to reach the last instance of "TYPE" then move out of the loop to continue the rest of the macro...all help is appreciated!

    Range("B56600").Select
    Selection.End(xlUp).Select
    endrow$ = ActiveCell.Row

    Range("G1").Select
    startRow$ = ActiveCell.Row
    Range("G" & startRow & ":G" & endrow).Select
    Selection.Find(What:="TYPE", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-5, -1).Select
    Selection.Copy
    ActiveCell.Offset(7, -5).PasteSpecial
    
    Do While ActiveCell.Value <> Empty

    ActiveCell.Offset(0, 6).Select
    startRow$ = ActiveCell.Row
    Range("G" & startRow & ":G" & endrow).Select
    Selection.Find(What:="TYPE", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-5, -1).Select
    Selection.Copy
    ActiveCell.Offset(7, -5).PasteSpecial
        
    Loop

I think the problem might be with where I put the "Do While ActiveCell.Value <> Empty" line. And yes, I know my code is messy (kind of like my life...lol) so appreciate any extra help as well.

Thanks for looking!
~ZM~

 

[DanteAmor]

You can explain with an example or the sequence of steps you need to do.


Only as information, when you put a number in a variable with $, like this:
endrow $ = ActiveCell.Row
You are storing the number but as a text, if later you need the value as a number, you may have problems.

 

[DanteAmor]

Try with the following:

Execute the code step by step from VBA using F8 to see the operation of the code and review the results.

Sub Test2()
    Dim r As Range, b As Range
    Dim cell As String
    
    Set r = Range("G:G")
    Set b = r.Find("TYPE", LookIn:=xlValues, lookat:=xlPart)
    If Not b Is Nothing Then
        cell = b.Address
        Do
            Cells(b.Row + 2, "A") = Cells(b.Row - 5, "F")
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If
End Sub

 

[zombiemaster]

Thanks so much for the code, Dante. I don't pretend to UNDERSTAND it, but it works like a charm! THANK YOU!

~ZM~

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.