Continuously running recorded macro through table

[smichael]

I have the recorded macro down, but I would like it to run continuously down the column through the range of data and stop at the desired cell instead of having to click the shortcut for every row. Is there a way to do this?

 

[Desu Nota from Columbus]

yes, post your macro and I can help you out.

 

[smichael]

This is code from recording changing the data of one cell based on another via copy and paste if you're confused.

Sub Change2()
'
' Change2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -4).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[4]-RC[3]"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -4).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 3).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.Offset(1, -3).Range("A1").Select
End Sub

I'd like the code to run all the way down the column up until the end (row 1752)

 

[Desu Nota from Columbus]

What is the ActiveCell? The cell you are copying.


And can you explain in words what is happening? is there a pattern to where you are pasting?

 

[smichael]

Its basically changing the value of a cell by subtracting another value from it, while keeping it in that cell.

What I'm doing is copying a number (G7), pasting it in K7, going back to G7 and subtracting K7 from another value (J7). I then copy G7 again, paste its value in K7 and then copy and paste it back (I will delete the entire K row afterwards, so G7 cannot reference that in the formula). Lastly, it changes J7 back to zero.

I know thats a little bit confusing lol, but its the best way without showing you.
​

 

[smichael]

and yes there is a pattern, i will be doing the same thing going down the chart until there is no more data

 

[Desu Nota from Columbus]

I can't make sense out of this sorry. I don't know how to help you.

 

[smichael]

is there a simple way to have the macro continue down the column without pushing the shortcut button?

 

[Desu Nota from Columbus]

You have to create a loop, but I don't understand where to start the loop because you used ActiveCell as the starting point.

Here is an example of a loop:

Sub test()
Dim i As Long
Dim lrow as Long
Application.ScreenUpdating = False

lrow = Rows("G" & Rows.Count).End(xlUp).Row `Finds the last row with data in Column G
    
  For i = 7 to lrow  `Macro will run continuously until lrow is found (the last row in Column G)

        If Range("G" & i) = 0 Then

             Range("H" & i).Formula = "=Today()+7"  `This is what the macro is changing, putting this formula in H7:H(lrow)

        End If

  Next i

Application.ScreenUpdating = True

End Sub

This code will loop from G7 until the last record in Column G (called lrow), applying the formula to all cells in H7--> H# that meets the criteria.

It doesn't start by using a preselected cell, the starting point is defined in the macro (i = 7, so start at G7). Make sense?

 

[smichael]

Yeah that makes sense. I switched it up a little bit so that it always starts at G7, but I'm not sure where I would put the loop in at.

Here is my code:

Sub Change2()
'
' Change2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Range("G7").Select
    Selection.Copy
    Range("K7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[4]-RC[3]"
    Range("G7").Select
    Selection.Copy
    Range("K7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("G7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
End Sub