Microsoft Excel: Macro to repeat a specific action multiple times

Question

My task is to use Excel to manipulate a large set of data and I had heard of using a Macro but I'm not very code-savvy. I recorded the steps that are required using the macro function, but I need to add more lines of code to utilize looping and making sure it advances by 2 after every repeat.

I've posted my steps below:

Range("A5:C5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+0.1"
Range("B7:C7").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Basically, select 3 cells (A5:C5) insert cells and shift cells down. Use a formula in the newly empty A5 to add 0.1 to A4 and copy values from B7:C7 and paste into B5:C5.

The following image shows a before and after of what I'm talking about to make things more clear.

Before

After

The next step would be:

Range("A7:C7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A7").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+0.1"
Range("B9:C9").Select
Selection.Copy
Range("B7").Select
ActiveSheet.Paste
Application.CutCopyMode = False

and so on.

Any help with this would be greatly appreciated. I also apologize if what I'm asking is still confusing or not clear in any way.

Answer 1

_[Prologue:]

Hi, I'll provide you with an answer and I tried to comment the heck out of it to make it as beginner friendly as possible, but the truth of the matter is:

I can explain to you how it's done, but you will never properly understand why it's done until you properly understand basic programming methodologies such as looping and that is something only you and you alone have to sit down to and fully comprehend

---

_{[The gaps in logic:]}

Probably the biggest issue is, you have not specified what happens when your data reaches empty cells (what I mean under that) - if in your loop you were on row 10 (7, M, N) you would have no longer any letters to add, as the next 2 rows (12) no longer contain eny data.

Given this, I modified the loop to start at the n-2th row instead to prevent this from happening (that means in your example it will end (or start to be more precise) at 6.1 as it's the last row that can retrieve the data)

In general, I'd recommend posting not only a picture of input data, but rather than picture of current result in this case a properly explained expected result would be much more to our benefit and understanding and would have saved me a lot of the guesswork here.

---

_{[The theoretical part of your question:]}

I'll save you a bit of googling time here with few useful facts.

1. If you're looking to repeat a specific action, you should always be looking to utilize one of the 2 (or 3 depending on how you classify them) loops for and do (while/until)
2. Better yet, if you're looking to loop a variant amount of actions for repeated amount of times, you should utlize either a procedure Sub or a function Function so you can use arguments that act as a variable for the loop.
3. Generally when adding or removing rows it's important to loop from Bottom to Top (from Last to First). Why? Because if you add an extra row, it's going to mess up your Row order.

With all that in mind, the procedure itself could look something like this:

---

_{[The solution itself:]}

You can use the following procedure every time for a specified range.

Option Explicit 'prevents typo. errors, undeclared variables and so on

Private Sub extra_row(ByVal rng As Range) 'declaration of procedure

 Dim i As Long
 ' we loop for the n-th - 2 row (last row - 2) to the pre-first (added) row.
 For i = (rng.Rows.Count + rng.Row - 2) To rng.Row + 1 Step -1
 'why the -2? ^ Because if you add the letters from next 2 rows,_
  the last 2 would have no to grab _
  eg. Row 10 and 11 in your original data would have no data under them to grab

 ' a bit harder section to comprehend, if it's the first data entry _
   we need to account for the fact that we have not added any extra rows yet_
   hence we also need to decrement the row from which we receive the data by 1 _
   it 's bit of difficult to word, i'd highly recommend debugging yourself _
   and seeing what happens with or without it
  Dim fp As Integer
    If (i - 2 = rng.Rows.Count) Then
      fp = 1
    Else
      fp = 0
    End If

    ' now we just add the extra rows where we can
    Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(i, 1) = Cells(i, 1).Offset(-1, 0) + 0.1 'we add 0.1 to cell one above
    Cells(i, 2) = Cells(i + 3 - fp, 2) ' similar case, with letters, but two below
    Cells(i, 3) = Cells(i + 3 - fp, 3) ' similar case, with letters, but two below
  Next i 'and we loop for every cell in our specified range

End Sub

Eg. in your case, you could run the procedure with the following command:

Call extra_row(Range("A4:A11"))

---

_{[Practical use]}

While the solution itself should work, in a real world example it probably might be smarter not to use a specific range to call for each procedure. Especially if that means use has to look at the worksheet, check the range and count the rows manually.

That's one of the reasons we created a procedure here to begin with. So I created one more procedure akin to main() in most programming languages which detects the last active row and applies the procedure to your data range by detecting it automatically.

So in the end, your could should look something like this:

Option Explicit
Private Sub extra_row(ByVal rng as Range)
  '... code from the answer here
End Sub

Private Sub rundata()
    Dim lr As Long
    lr = Sheets("Your Sheet Name").Cells(Rows.Count, 1).End(xlUp).Row 
    'detects the last active (nonempty) row _
     rememeber to change the  Sheets("") to wherever your data is stored

    Dim mydata As Range
    Set mydata = Range("A4:A" & lr) 'under presumption your data always begins with A4

    Call extra_row(mydata)     
End Sub

Now, whenever you would run (manally) or Call the run_data() procedure it would automatically detect the range and apply the procedure we defined to it.

---

_{[Expected result visualization and closing words:]}

We started with this:

After running the procedure:

Now I know, it may seem like there's a lot of novel concepts here, but truth of the matter is, all of them are fairly easy once you buckle down and try to comprehend the code line by line. Most of it is simple mathematical operations.

If you still have trouble comprehending anything here, do your own research first and then post comment here or better yet, create a new question (if it warrants it).

---

Good luck on your coding journey! :)
Rawrplus

Answer 2

This code should do the trick.
The code gives you an InputBox in which you can type in the number of times to run the code.

Sub test()
Application.ScreenUpdating = False
Dim Grab As Range
Dim RunTimes As Long

On Error GoTo Get_Out
RunTimes = InputBox("How many times shall the code run?", "Run times")
On Error GoTo 0

For x = 1 To RunTimes * 1.5 + 3 Step 2
    Set Grab = ActiveSheet.Range("A" & x + 4)
    Grab.EntireRow.Insert
    Grab.Offset(-1, 0).Value = Grab.Offset(-2, 0).Value + 0.1
    Grab.Offset(-1, 1).Value = Grab.Offset(1, 1).Value
    Grab.Offset(-1, 2).Value = Grab.Offset(1, 2).Value
Next x

MsgBox "Succes"

Get_Out:
Application.ScreenUpdating = True
End Sub

Let me know if you have any questions about the code or if you want me to explain it further :)