• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code

W

woodduck

New Member
Hi all,

I recently discovered this site as I am learning more about excel. Hope you can help me with my problem.


I currently have a spreadsheet with 26 columns and 3849 columns.

I am trying to insert two rows in between each of the current rows. After that I want to move the data from O3 to C4 and then c5=C3-C4. I already have some of the code but for some reason it only inserts one row in between each of the current rows and it doesn't loop. I had a friend right this code so i have no idea how this works.


Below is the code that will insert one row between each row and will copy the correct cells to where i want them, and it will also do the subtraction.

I need to make it so it inserts two rows. Please help!

Sub addrow()

LastRow = Application.WorksheetFunction.CountA(Range("A:A"))

Application.ScreenUpdating = False

For x = 0 To LastRow
ThisWorkbook.Worksheets("Sheet6").Range("C4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("O3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("A4").Offset(x + 2 * x, 0).EntireRow.Insert

ThisWorkbook.Worksheets("Sheet6").Range("C4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("O3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("D4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("P3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("E4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("Q3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("F4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("R3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("G4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("S3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("H4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("T3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("I4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("U3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("J4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("V3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("K4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("W3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("L4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("X3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("M4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("Y3").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("N4").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("Z3").Offset(x + 2 * x, 0)

ThisWorkbook.Worksheets("Sheet6").Range("C5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("C3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("C4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("D5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("D3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("D4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("E5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("E3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("E4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("F5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("F3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("F4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("G5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("G3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("G4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("H5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("H3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("H4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("I5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("I3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("I4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("J5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("J3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("J4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("K5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("K3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("K4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("L5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("L3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("L4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("M5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("M3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("M4").Offset(x + 2 * x, 0)
ThisWorkbook.Worksheets("Sheet6").Range("N5").Offset(x + 2 * x, 0) = ThisWorkbook.Worksheets("Sheet6").Range("N3").Offset(x + 2 * x, 0) - ThisWorkbook.Worksheets("Sheet6").Range("N4").Offset(x + 2 * x, 0)

Next x

End Sub
 
Welcome to the forum!

This post may get long, but I'm going to try and explain what happens with the code. First, there's a lot of duplicate text. When ever you have code that writes the same thing over and over again, there's probably a better way to write it.

First off, we'll make things easier to read by using a
Code: 
With
statement. This lets us use shorthand for all the contained code like so:

Sub addrow()
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet6")
LastRow = Application.WorksheetFunction.CountA(.Range("A:A"))
For x = 0 To LastRow
.Range("C4").Offset(x + 2 * x, 0) = .Range("O3").Offset(x + 2 * x, 0)
.Range("A4").Offset(x + 2 * x, 0).EntireRow.Insert

.Range("C4").Offset(x + 2 * x, 0) = .Range("O3").Offset(x + 2 * x, 0)
.Range("D4").Offset(x + 2 * x, 0) = .Range("P3").Offset(x + 2 * x, 0)
.Range("E4").Offset(x + 2 * x, 0) = .Range("Q3").Offset(x + 2 * x, 0)
.Range("F4").Offset(x + 2 * x, 0) = .Range("R3").Offset(x + 2 * x, 0)
.Range("G4").Offset(x + 2 * x, 0) = .Range("S3").Offset(x + 2 * x, 0)
.Range("H4").Offset(x + 2 * x, 0) = .Range("T3").Offset(x + 2 * x, 0)
.Range("I4").Offset(x + 2 * x, 0) = .Range("U3").Offset(x + 2 * x, 0)
.Range("J4").Offset(x + 2 * x, 0) = .Range("V3").Offset(x + 2 * x, 0)
.Range("K4").Offset(x + 2 * x, 0) = .Range("W3").Offset(x + 2 * x, 0)
.Range("L4").Offset(x + 2 * x, 0) = .Range("X3").Offset(x + 2 * x, 0)
.Range("M4").Offset(x + 2 * x, 0) = .Range("Y3").Offset(x + 2 * x, 0)
.Range("N4").Offset(x + 2 * x, 0) = .Range("Z3").Offset(x + 2 * x, 0)

.Range("C5").Offset(x + 2 * x, 0) = .Range("C3").Offset(x + 2 * x, 0) - .Range("C4").Offset(x + 2 * x, 0)
.Range("D5").Offset(x + 2 * x, 0) = .Range("D3").Offset(x + 2 * x, 0) - .Range("D4").Offset(x + 2 * x, 0)
.Range("E5").Offset(x + 2 * x, 0) = .Range("E3").Offset(x + 2 * x, 0) - .Range("E4").Offset(x + 2 * x, 0)
.Range("F5").Offset(x + 2 * x, 0) = .Range("F3").Offset(x + 2 * x, 0) - .Range("F4").Offset(x + 2 * x, 0)
.Range("G5").Offset(x + 2 * x, 0) = .Range("G3").Offset(x + 2 * x, 0) - .Range("G4").Offset(x + 2 * x, 0)
.Range("H5").Offset(x + 2 * x, 0) = .Range("H3").Offset(x + 2 * x, 0) - .Range("H4").Offset(x + 2 * x, 0)
.Range("I5").Offset(x + 2 * x, 0) = .Range("I3").Offset(x + 2 * x, 0) - .Range("I4").Offset(x + 2 * x, 0)
.Range("J5").Offset(x + 2 * x, 0) = .Range("J3").Offset(x + 2 * x, 0) - .Range("J4").Offset(x + 2 * x, 0)
.Range("K5").Offset(x + 2 * x, 0) = .Range("K3").Offset(x + 2 * x, 0) - .Range("K4").Offset(x + 2 * x, 0)
.Range("L5").Offset(x + 2 * x, 0) = .Range("L3").Offset(x + 2 * x, 0) - .Range("L4").Offset(x + 2 * x, 0)
.Range("M5").Offset(x + 2 * x, 0) = .Range("M3").Offset(x + 2 * x, 0) - .Range("M4").Offset(x + 2 * x, 0)
.Range("N5").Offset(x + 2 * x, 0) = .Range("N3").Offset(x + 2 * x, 0) - .Range("N4").Offset(x + 2 * x, 0)

Next x
'AND THIS CLOSING LINE
End With
End Sub
Next, I see you've got the x+2*x thing written a lot. The output of this equation is the same as counting by 3, so we'll change the For statement and shorten things up more:

[pre]
Code: 
Sub addrow()

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet6")
LastRow = Application.WorksheetFunction.CountA(.Range("A:A"))
For x = 0 To LastRow * 3 Step 3
.Range("C4").Offset(x, 0) = .Range("O3").Offset(x, 0)
.Range("A4").Offset(x, 0).EntireRow.Insert

.Range("C4").Offset(x, 0) = .Range("O3").Offset(x, 0)
.Range("D4").Offset(x, 0) = .Range("P3").Offset(x, 0)
.Range("E4").Offset(x, 0) = .Range("Q3").Offset(x, 0)
.Range("F4").Offset(x, 0) = .Range("R3").Offset(x, 0)
.Range("G4").Offset(x, 0) = .Range("S3").Offset(x, 0)
.Range("H4").Offset(x, 0) = .Range("T3").Offset(x, 0)
.Range("I4").Offset(x, 0) = .Range("U3").Offset(x, 0)
.Range("J4").Offset(x, 0) = .Range("V3").Offset(x, 0)
.Range("K4").Offset(x, 0) = .Range("W3").Offset(x, 0)
.Range("L4").Offset(x, 0) = .Range("X3").Offset(x, 0)
.Range("M4").Offset(x, 0) = .Range("Y3").Offset(x, 0)
.Range("N4").Offset(x, 0) = .Range("Z3").Offset(x, 0)

.Range("C5").Offset(x, 0) = .Range("C3").Offset(x, 0) - .Range("C4").Offset(x, 0)
.Range("D5").Offset(x, 0) = .Range("D3").Offset(x, 0) - .Range("D4").Offset(x, 0)
.Range("E5").Offset(x, 0) = .Range("E3").Offset(x, 0) - .Range("E4").Offset(x, 0)
.Range("F5").Offset(x, 0) = .Range("F3").Offset(x, 0) - .Range("F4").Offset(x, 0)
.Range("G5").Offset(x, 0) = .Range("G3").Offset(x, 0) - .Range("G4").Offset(x, 0)
.Range("H5").Offset(x, 0) = .Range("H3").Offset(x, 0) - .Range("H4").Offset(x, 0)
.Range("I5").Offset(x, 0) = .Range("I3").Offset(x, 0) - .Range("I4").Offset(x, 0)
.Range("J5").Offset(x, 0) = .Range("J3").Offset(x, 0) - .Range("J4").Offset(x, 0)
.Range("K5").Offset(x, 0) = .Range("K3").Offset(x, 0) - .Range("K4").Offset(x, 0)
.Range("L5").Offset(x, 0) = .Range("L3").Offset(x, 0) - .Range("L4").Offset(x, 0)
.Range("M5").Offset(x, 0) = .Range("M3").Offset(x, 0) - .Range("M4").Offset(x, 0)
.Range("N5").Offset(x, 0) = .Range("N3").Offset(x, 0) - .Range("N4").Offset(x, 0)

Next x
End With
End Sub
[/pre]
Next, let's write some loops to shorten the 2 big sections. We'll also go ahead and define some of the variables we're using:

Sub addrow()
Dim c As Range
Dim x As Long

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet6")
LastRow = Application.WorksheetFunction.CountA(.Range("A:A"))

For x = 0 To LastRow * 3 Step 3
.Range("C4").Offset(x, 0) = .Range("O3").Offset(x, 0)
.Range("A4").Offset(x, 0).EntireRow.Insert

For Each c In .Range("C4:N4")
c.Offset(x, 0) = c.Offset(x - 1, 12)
Next c

For Each c In .Range("C5:N5")
c.Offset(x, 0) = .Offset(x - 2, 0) - .Offset(x - 1, 0)
Next c

Next x
End With

'Make sure we turn this back on
Application.ScreenUpdating = True
End Sub
There! Much easier to read what all is going on. However, at this point, rather than continuing to use all these offsets and keeping track of where we are in a single cell, we can start using Range references, and things really get simplified. We'll also switch the direction we go through our counting loop so that the inserted rows don't mess us up.

[pre]
Code: 
Sub addRows()
Dim LastRow As Long
Application.ScreenUpdating = False

With ThisWorkbook.Worksheets("Sheet6")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

'We're going to go through each row
'but since we are inserting rows, we'll step backwards so
'we don't lose count
For x = LastRow To 3 Step -1
'Insert 2 rows
.Cells(x + 1, "A").Resize(2, 1).EntireRow.Insert

'Transfer from O:Z to C:N
.Range(.Cells(x, "O"), .Cells(x, "Z")).Copy .Cells(x + 1, "C")

'Add formulas to all our cells
.Range(.Cells(x + 2, "C"), .Cells(x + 2, "N")).FormulaR1C1 _
= "=R[-2]C-R[-1]C"
Next x
End With

'Make sure we turn this back on
Application.ScreenUpdating = True

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top