Copy and Paste 'X' rows

[Guanjin Peter]

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?

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

 

[Guanjin Peter]

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)

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
	V	W	X	Y
2	-		FG	FG-MEAT
3	-		FG	FG-MEAT
4	-		FG	FG-MEAT
5	-		FG	FG-VEGE
6	-		FG	FG-MEAT
7	-		FG	FG-MEAT
8	-		FG	FG-MEAT
9	-		FG	FG-MEAT
10	-		FG	FG-MEAT
11	-		FG	FG-MEAT
12	-		FG	FG-MEAT
13	-		FG	FG-MEAT
14	-		FG	FG-MEAT
15	-		FG	FG-MEAT
16	-		FG	FG-MEAT
17	-		FG	FG-MEAT
18	-		FG	FG-MEAT
19	-		FG	FG-MEAT
20	-		FG	FG-MEAT
21	-		FG	FG-MEAT
22	-		FG	FG-MEAT
23	-		FG	FG-MEAT
24	-		FG	FG-MEAT
25	-		FG	FG-MEAT
26	-		FG	FG-MEAT
27	-		FG	FG-MEAT
28	22.88		FG	FG-MEAT
SOURCE FR NAV

 

[Michael M]

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]

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

 

[Michael M]

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]

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]

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]

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)"

 

[Guanjin Peter]

**

 

[Andrew Poulsom]

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)"