Set 'Do Until' Endpoint for Excel VBA Macro Loop

Question

I know almost nothing about VBA and need some help! I've recorded a simple macro that will insert a row and perform some relative cut/paste when a certain value ("Choice") is found in column B. I would like this macro to loop until it reaches the end of the data set (keep in mind part of the macro inserts more rows as it goes). I've gotten it to loop and do what I want, but I can't figure out how to make it stop and not be infinite. Searching for blanks will not help as there are several blanks within the data set. Hoping for a helpful Do Until code? If you have a solution, can you please append it to my macro in your reply so I can see how the whole thing would look? Thank You!!

Sub Macro6()
'
' Macro6 Macro
' Spacer
'
' Keyboard Shortcut: Ctrl+q
'
    Dim c As Range

   For Each c In Range("B1:B3000")
    Cells.Find(What:="choice", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(1, 0).Range("A1:B1").Select
    Selection.Cut
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next
End Sub

OldUgly · Accepted Answer · 2016-04-08 23:21:16Z

1

Sub Macro6()
'
' Macro6 Macro
' Spacer
'
' Keyboard Shortcut: Ctrl+q
'
Dim c As Range
Dim lastRow As Long

With ActiveSheet

    lastRow = .Cells(.Rows.count, "B").End(xlUp).Row

    For I = lastRow To 1 Step -1

        Debug.Print .Cells(I, 2).Value
        If InStr(1, .Cells(I, 2).Value, "choice") > 0 Then

            .Cells(I, 2).Rows("1:1").EntireRow.Select
            Selection.Insert Shift:=xlDown
            ActiveCell.Offset(1, 0).Range("A1:B1").Select
            Selection.Cut
            ActiveCell.Offset(-1, 0).Range("A1").Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, 0).Range("A1").Select

        End If

    Next

End With

End Sub

When I run this code against this input ...

I get this output ...

Is this the result you were looking for? If not, can you provide a better description of the result you need?

edited Apr 8, 2016 at 23:21

OldUgly

2,1393 gold badges14 silver badges22 bronze badges

answered Apr 8, 2016 at 21:41

findwindow

3,2081 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

fouraces Over a year ago

Hmmm, that one doesn't seem to do anything. Cursor doesn't even move.

fouraces Over a year ago

Whenever "choice" occurs in column B, insert a row above, and then move the values from columns A and B up one row into the new row

findwindow Over a year ago

move the values from columns A and B up one row into the new row the values from the row where choice occured? New row meaning the one just inserted?

fouraces Over a year ago

Hmm, still nothing. I don't get an error message, it just doesn't do anything. Again, I know nothing about VBA, but just wondering- why are we calling Dim c as Range if we don't use it in the rest of the code? Appreciating your help!

findwindow Over a year ago

Is the sheet in question active? Does column B contain "choice"? Yea we don't need c. Forgot about it XD

|

Collectives™ on Stack Overflow

Set 'Do Until' Endpoint for Excel VBA Macro Loop

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related