excelvbapause

Pausing a macro repeatedly for data entry


Sub COLUMN_C()
'
' COLUMN_C Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
ActiveCell.FormulaR1C1 = "P"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "25"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(1, -5).Range("A1").Select
End Sub

I need this macro to pause for "data" and take me to the next cell down so I can start all over again and hopefully loop 'COLUMN_C' so I can start from, say, C8 and just keep going down the column until the job's done or my fingers grow numb. Love Excel but know very little about editing macros and making them work harder!

I looked at several online suggestions and could not understand any of them. It's a simple macro doing a very important job and I'm hoping that an expert can show me how to put pauses in place of "P" and "25" and "2" and speed up the work I am doing which right now is rewarding but tedious.


Solution

  • This code

    Sub selectCells()
        Dim r As Long
        Dim c As Long
        Do
            r = ActiveCell.Row
            c = ActiveCell.Column
            Call waitForInput(ActiveCell.Address)
            If Cells(r, c).Value = "stop" Then Exit Sub
            Cells(r, c + 3).Select
            Call waitForInput(ActiveCell.Address)
            Cells(r, c + 5).Select
            Call waitForInput(ActiveCell.Address)
            Cells(r + 1, c).Select
        Loop
    End Sub
    
    Sub waitForInput(start As String)
        Do While ActiveCell.Address = start
            DoEvents
        Loop
    End Sub
    

    will

    1. wait until you enter something in the active cell
    2. wait again until you enter something in the cell 3 cells to the right of that
    3. wait again until you enter something in the cell 2 cells to the right of that

    It will then move to the next row (immediately under the originally active cell of the previous row) and repeat the process (unless you enter stop in that first cell).