I have a worksheet with multiple dynamic “cost categories”.
The code I am working on searches cells in column “B”. If Cell contains the string “Subtotal”, then sum columns (I:S). The rows above are dynamic in each cost category.
Lastly, there should be a Total Row at the bottom of the sheet with a Sum of all Subtotals.
I have attached a sample worksheet with code.
- Sub SubtotalRng()
- Dim i As Long
- Dim lr As Long
- Dim lrCount As Long
- specificText = "SubTotal" ' You can change this
- With ActiveSheet
- lr = .Cells(Rows.Count, 1).End(xlUp).Row
- For i = lr To 4 Step -1 ' last row and work your way to the top - row # at top of sheet
- If UCase(.Cells(i, 2)) Like "*" & UCase(specificText) & "*" Then
- Set lrCount = Cells(Rows.Count, i, -1).Offset(, 8).End(xlUp) 'This does not work
- 'Debug.Print lrCount
- .Cells(i, 9).FormulaR1C1 = "=Sum (RC[-1]:RC[-" & lrCount & "]" ' This should add R1C1 formula in columns (I:S)
- End If
- 'Set lrCount = Nothing
- End With
- End Sub