Subtotal Macro Formula Help

[mzsuga]

Attach is a sample worksheet. It has simple subtotals on it. But I dont want the subtotal to sum every column. For the cash% column, i want it to be the subtotal of the cash column divided by the subtotal of the credit column. For the tax% subtotal, I want it to be the subtotal of the tax column divded by the subtotal of the cash column.

Is there a macro for this? Like instead of the subtotal summing it, can it be divide?

[Bob Phillips]

Sub AdjustTotals()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "A") Like "*Total*" Then

.Cells(i - 1, "H").Copy
.Cells(i, "H").PasteSpecial xlPasteFormulas
.Cells(i - 1, "J").Copy
.Cells(i, "J").PasteSpecial xlPasteFormulas
End If
Next i
End With
End Sub

[mzsuga]

Sub AdjustTotals()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "A") Like "*Total*" Then

.Cells(i - 1, "H").Copy
.Cells(i, "H").PasteSpecial xlPasteFormulas
.Cells(i - 1, "J").Copy
.Cells(i, "J").PasteSpecial xlPasteFormulas
End If
Next i
End With
End Sub


Thank you but that just copies the last row before the subtotal of column h and j to the row of the subtotal but i want the subtotal of column H to be the subtotal of column E Divided by the subtotal of column F, same for tax except its column J divided by E

[Bob Phillips]

It copies your formula, which ives you exactly the result that you describe, in a formula.

[mzsuga]

It copies your formula, which ives you exactly the result that you describe, in a formula.

you're right. But the sheet I have, it doesn't have the formulas. It is just values.

[Bob Phillips]

If you give us misleading info, you will get erroneous results.



Sub AdjustTotals()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "A") Like "*Total*" Then

.Cells(i, "H").FormulaR1C1 = "=RC[-3]/RC[-2]"
.Cells(i, "J").FormulaR1C1 = "=RC[-1]/RC[-5]"
End If
Next i
End With
End Sub