I forgot to mention, the macro I am trying to get working is in Module-1 and named "SubtotalRng"
This code is part of a larger project.
I am a cost estimator for a general contractor.
This workbook is will ultimately be a template for use by myself and others..... I think.
The user will be able to build the estimate by doing the following-
- Click on a button to add a new "Cost Code" with a single row, in that row, the user can add a description or task.
- Another button will add an additional row within the Cost Code category to add tasks. Rows are inserted with formulas.
- additional buttons for various views.
I have attached the full worksheet below to hopefully give you a better understanding of what I am trying to do.
I have successfully written, or I should say searched for and modified code gleaned from this forum and others to get the worksheet to the point I described above.
And now I'm stuck!
I am not a coder, but I love what you guys do. It's the magic behind the scenes.
Why do I need code to enter formulas? Call it the next step in my quest to better understand this magic called VBA.
If what I am trying to do with the subtotal is poor judgment, then please, point me in the right direction.
Thanks in advance for any input
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.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