Subtotal Macro but keep existing formula for one column

[Emb21]

Greetings,

Please see below subtotal macro, I just want to sum columns 7 thru 10 and 12 but col 11 should use existing formula =IF(G10<>0,J10/G10," N/A"). Any help would be appreciated

Sub aSubTotal() 'Excel VBA to create subtotals.
Dim iCol As Integer
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False
i = 10
j = i
'Sort the data so like data is grouped together.
Range("D10").CurrentRegion.Offset(1).Sort Range("D11"), 1
'Loops throught Col D Checking for match then when there is no match add Sum
Do While Range("D" & i) <> ""
If Range("D" & i) <> Range("D" & (i + 1)) Then
Rows(i + 1).Insert
Range("D" & (i + 1)) = "Subtotal " & Range("D" & i).Value
For iCol = 7 To 12 'Columns to Sum
Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & i & "C)"
Next iCol
Range(Cells(i + 1, 1), Cells(i + 1, 12)).Font.Bold = True
i = i + 2
j = i
Else
i = i + 1
End If
Loop

Thank you,
Eric

 

[shknbk2]

I added a check to see if iCol = 11, make it 12 before the formula is set.

Sub aSubTotal() 'Excel VBA to create subtotals.
    Dim iCol As Integer
    Dim i As Integer
    Dim j As Integer
    
    Application.ScreenUpdating = False
    i = 10
    j = i
    'Sort the data so like data is grouped together.
    Range("D10").CurrentRegion.Offset(1).Sort Range("D11"), 1
    'Loops throught Col D Checking for match then when there is no match add Sum
    Do While Range("D" & i) <> ""
        If Range("D" & i) <> Range("D" & (i + 1)) Then
            Rows(i + 1).Insert
            Range("D" & (i + 1)) = "Subtotal " & Range("D" & i).Value
            For iCol = 7 To 12 'Columns to Sum
                If iCol = 11 Then
                    iCol = 12
                End If
                Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & i & "C)"
            Next iCol
            Range(Cells(i + 1, 1), Cells(i + 1, 12)).Font.Bold = True
            i = i + 2
            j = i
        Else
            i = i + 1
        End If
    Loop
End Sub

 

[Emb21]

Thank you!!