COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A"
 

Hi all, I have macro set on a button (see below) in my Sheet2

Sub FORMULAS()
Dim ic As Long
With Sheets("SPLIT")
.Range("G2").FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
.Range("H2").FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",RC[-1])"
.Range("I2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUMIF(R2C7:R50000C7,RC[-1],R2C5:R50000C5))"
.Range("J2").FormulaR1C1 = "=ROUND(RC[-5],0)"
.Range("K2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("L2").FormulaR1C1 =
"=IF(RC[-4]="""","""",SUMIF(R2C7:R50000C7,RC[-4],R2C11:R50000C11))"
.Range("M2").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4]-RC[-1])"
.Range("N2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUM(RC[-3],RC[-1]))"
.Range("O2").FormulaR1C1 = "=IF(RC[-1]="""",RC[-4],RC[-1])"
.Range("P2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("Q2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("R2").FormulaR1C1 =
"=IF(RC[-10]="""","""",SUMIF(R2C7:R50000C7,RC[-10],R2C17:R50000C17))"
ic = Cells(Rows.Count, "A").End(xlUp).Row
.Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic -
1)
Application.CutCopyMode = False
End With

End Sub

I get error when i run the macro and this line (see below) get
highligted in module
..Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic - 1)
basically i am try to put formulas by macro in cells from G2 to R2 of
Sheet("SPLIT") and then i want macro to copy those formulas from
range G3 to R3 down to last value cell in column A. Please can
anybody tell that how can i correct my macro above or what am i doing
wrong? Thanks in advance for help

COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A"
 

Hi

Look at this

Sub FORMULAS()
Dim ic As Long
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row

With Sheets("SPLIT")
.Range("G2").FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
.Range("H2").FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",RC[-1])"
.Range("I2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUMIF(R2C7:R50000C7,RC[-1],R2C5:R50000C5))"
.Range("J2").FormulaR1C1 = "=ROUND(RC[-5],0)"
.Range("K2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("L2").FormulaR1C1 =
"=IF(RC[-4]="""","""",SUMIF(R2C7:R50000C7,RC[-4],R2C11:R50000C11))"
.Range("M2").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4]-RC[-1])"
.Range("N2").FormulaR1C1 = "=IF(RC[-1]="""","""",SUM(RC[-3],RC[-1]))"
.Range("O2").FormulaR1C1 = "=IF(RC[-1]="""",RC[-4],RC[-1])"
.Range("P2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("Q2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("R2").FormulaR1C1 =
"=IF(RC[-10]="""","""",SUMIF(R2C7:R50000C7,RC[-10],R2C17:R50000C17))"
ic = Cells(Rows.Count, "A").End(xlUp).Row
.Range("G2:R2").Copy .Range("G3", .Cells(LastRow, "R"))
Application.CutCopyMode = False
End With

End Sub

Regards,
Per

"K" skrev i meddelelsen
...
Hi all, I have macro set on a button (see below) in my Sheet2

Sub FORMULAS()
Dim ic As Long
With Sheets("SPLIT")
.Range("G2").FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
.Range("H2").FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",RC[-1])"
.Range("I2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUMIF(R2C7:R50000C7,RC[-1],R2C5:R50000C5))"
.Range("J2").FormulaR1C1 = "=ROUND(RC[-5],0)"
.Range("K2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("L2").FormulaR1C1 =
"=IF(RC[-4]="""","""",SUMIF(R2C7:R50000C7,RC[-4],R2C11:R50000C11))"
.Range("M2").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4]-RC[-1])"
.Range("N2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUM(RC[-3],RC[-1]))"
.Range("O2").FormulaR1C1 = "=IF(RC[-1]="""",RC[-4],RC[-1])"
.Range("P2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("Q2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("R2").FormulaR1C1 =
"=IF(RC[-10]="""","""",SUMIF(R2C7:R50000C7,RC[-10],R2C17:R50000C17))"
ic = Cells(Rows.Count, "A").End(xlUp).Row
.Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic -
1)
Application.CutCopyMode = False
End With

End Sub

I get error when i run the macro and this line (see below) get
highligted in module
.Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic - 1)
basically i am try to put formulas by macro in cells from G2 to R2 of
Sheet("SPLIT") and then i want macro to copy those formulas from
range G3 to R3 down to last value cell in column A. Please can
anybody tell that how can i correct my macro above or what am i doing
wrong? Thanks in advance for help