Help needed with Do Loop

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
179
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!

Code:
    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

Well-known Member
Joined
Dec 3, 2018
Messages
9,536
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,536
Office Version
2007
Platform
Windows
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.

Code:
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

Board Regular
Joined
Oct 27, 2009
Messages
179
Thanks so much for the code, Dante. I don't pretend to UNDERSTAND it, but it works like a charm! THANK YOU!

~ZM~
:cool:
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,536
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,084,776
Messages
5,379,826
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top