G
Guest
Hi
I have data in Cols B, C and D. The number of rows of data varies.
I'm trying to write a macro which puts a formula in E2 which sums D24 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.
I've got this far but it doesn't work as it puts the formula in every cell.
Please can I have some help
Thanks a lot
Kewa
----------------------------------
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"
Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown
Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False
Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<>"
End Sub
I have data in Cols B, C and D. The number of rows of data varies.
I'm trying to write a macro which puts a formula in E2 which sums D24 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.
I've got this far but it doesn't work as it puts the formula in every cell.
Please can I have some help
Thanks a lot
Kewa
----------------------------------
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"
Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown
Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False
Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<>"
End Sub