# Copy and Paste 'X' rows

#### Guanjin Peter

##### Active Member
I'm currently using the below code to add the formula in each cell. Depending on the number of rows.
It's very slow (Range("aa2").Value returns 1060) which means 1k rows. I was thinking of doing copy and paste to speed up. But I do not want to fill the whole column U (Column 13) with the formula. Is there anyway to set the max to copy to?
like r2:rX

where X is Range("aa2").Value?

Code:
``````Private Sub CommandButton1_Click()
Dim nDb As Integer
intRow = 2
Range("aa2").Value = "=COUNTA(A:A)-1"
nDb = Range("aa2").Value

Do While nDb > intRow
Var = "=IF(RC2=""Positive Adjmt."",+RC13,IF(RC2=""Negative Adjmt."",-RC13,""Invalid""))"
ActiveSheet.Range("u" & intRow).End(xlUp).Offset(1, 0).Resize(, 1) = Var
'Worksheets("nvT").Cells(intRow2, 4).Resize(, cpro).Value = qty

intRow = intRow + 1
Loop 'end of do-while
End Sub``````

Last edited:

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Guanjin Peter

##### Active Member
Currently came up with something via recording macro (please post yours, I'm open to all ideas)
notice in V column,
the D and the U values continues to grow.

=SUMIF(\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)
=SUMIF(\$D\$2:\$D1063,\$D2,\$U\$2:\$U1063)
=SUMIF(\$D\$2:\$D1064,\$D2,\$U\$2:\$U1064)
is there a way to fix it at that value?
=SUMIF(\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)
=SUMIF(\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)
=SUMIF(\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)

Code:
``````Private Sub CommandButton1_Click()
Dim nDb As Integer
intRow = 2
Range("aa2").Value = "=COUNTA(A:A)-1"
nDb = Range("aa2").Value
Var = "=IF(RC2=""Positive Adjmt."",+RC13,IF(RC2=""Negative Adjmt."",-RC13,""Invalid""))"
Range("U2") = Var
netVar = "=SUMIF(R2C4:R[" & nDb & "]C4,RC4,R2C21:R[" & nDb & "]C21)"
Range("V2") = netVar

Range("U2").Copy
Application.Goto Reference:="R2C21:R[" & nDb & "]C21"
ActiveSheet.Paste
Range("V2").Copy
Application.Goto Reference:="R2C22:R[" & nDb & "]C22"
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub``````
Book2.xls
VWXY
2-FGFG-MEAT
3-FGFG-MEAT
4-FGFG-MEAT
5-FGFG-VEGE
6-FGFG-MEAT
7-FGFG-MEAT
8-FGFG-MEAT
9-FGFG-MEAT
10-FGFG-MEAT
11-FGFG-MEAT
12-FGFG-MEAT
13-FGFG-MEAT
14-FGFG-MEAT
15-FGFG-MEAT
16-FGFG-MEAT
17-FGFG-MEAT
18-FGFG-MEAT
19-FGFG-MEAT
20-FGFG-MEAT
21-FGFG-MEAT
22-FGFG-MEAT
23-FGFG-MEAT
24-FGFG-MEAT
25-FGFG-MEAT
26-FGFG-MEAT
27-FGFG-MEAT
2822.88FGFG-MEAT
SOURCE FR NAV

#### Michael M

##### Well-known Member
Hi Peter
I posted this for someone else yesterday.
Sub Copydown()
Dim lrow As Long, r As Long
lrow = Worksheets("SHEETNAME").Cells(Rows.Count, "Y").End(xlUp).Row
For r = lrow To 1 Step -1
If Range("Y" & r).Value <> "" = True Then
Range("V" & r).Formula = "place formula here"
Else: Range("V" & r).Value = ""
End If
Next r
End Sub
So what it does is finds the last populated cell in column "Y" and then checks to see if there is something in cell "Y" and puts a formula in "V".
If there is nothing in cell "Y" then it leaves the cell in "V" Blank.

Regards
Michael M

#### Guanjin Peter

##### Active Member
hmm....interesting...hard to understand....where do I add it?

#### Michael M

##### Well-known Member
Firstly, you can change the name of the SUB() to your command Button macro.
Second, once you have put your first line of formulae in your spreadsheet, which is probably about here:
Range("V2") = netVar
my code will add the rest of the formulae
Third, if you want the value in "U" to remain the same, you need another dollar sign
Instead of this
\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)
Use this:
\$D\$2:\$D1062,\$D2,\$U\$2:\$U\$1062)

Regards
Michael M

#### Guanjin Peter

##### Active Member
according to your Q 3...where do I add the \$ sign?

netVar = "=SUMIF(R2C4:R[" & nDb & "]C4,RC4,R2C21:R[" & nDb & "]C21)"

add here? : "=SUMIF(R2C4:R[" ""\$"" & nDb & "]C4,RC4,R2C21:R[" ""\$"" & nDb & "]C21)"

tried alot of ways, didn't work...any idea?

#### Michael M

##### Well-known Member
Peter
You are using this range: (\$D\$2:\$D1062,\$D2,\$U\$2:\$U1062)
If you add another dollar sign after the last "U" in the equation.
(\$D\$2:\$D1062,\$D2,\$U\$2:\$U\$1062)
Here !

Regards
michael M

#### Guanjin Peter

##### Active Member
hmm, so I can only add after the formula is in the cell? Can I add it via VBA code like this?:
add here? : "=SUMIF(R2C4:R[" ""\$"" & nDb & "]C4,RC4,R2C21:R[" ""\$"" & nDb & "]C21)"

**

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
In R1C1 stye fotmulas the \$ is not used to fix references. Rather numbers in square brackets are relative and numbers without square brackets are absolute. If you want this in A2:

=SUMIF(\$D\$2:\$D\$1062,\$D2,\$U\$2:\$U\$1062)

where the variable nDb contains 1062, the VBA code would be:

Range("A2").FormulaR1C1 = "=SUMIF(R2C4:R" & nDb & "C4,RC4,R2C21:R" & nDb & "C21)"

Replies
1
Views
79
Replies
4
Views
128
Replies
4
Views
276
Replies
2
Views
205
Replies
1
Views
149

Threads
1,191,633
Messages
5,987,783
Members
440,110
Latest member
albertod8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back