In the spreadsheet, I sort by column A, then B, then C. I then insert a blank row after each change in value in those columns. I would like to add subtotals to each section. This is what I have tried. I get a "Method 'Range' of object '_Global' failed."
Please help.
Private Sub cmdUpdate_Click()
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A") <> Cells(i - 1, "A") _
And Cells(i, "A") <> "" _
And Cells(i - 1, "A") <> "" Then
Rows(i).Insert
Rows(i).Select
Selection.Interior.ColorIndex = xlNone
Range(Cells(i, "E")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
Range(Cells(i, "F")).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
End If
Next
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(i, "B") <> Cells(i - 1, "B") _
And Cells(i, "B") <> "" _
And Cells(i - 1, "B") <> "" Then
Rows(i).Insert
Rows(i).Select
Selection.Interior.ColorIndex = xlNone
Range(Cells(i, "E")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
Range(Cells(i, "F")).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
End If
Next
For i = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
If Cells(i, "C") <> Cells(i - 1, "C") _
And Cells(i, "C") <> "" _
And Cells(i - 1, "C") <> "" Then
Rows(i).Insert
Rows(i).Select
Selection.Interior.ColorIndex = xlNone
Range(Cells(i, "E")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
Range(Cells(i, "F")).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
End If
Next
End Sub
Bookmarks