Looking for some help…. I am using the following macro to insert a blank row (to separate varying Group rows) and then, Subtotal Column B per each Group:
Sub GroupInsertRowAndTotal()
Dim lngRow As Long, lngStart As Long
lngStart = 2: lngRow = lngStart
Do: lngRow = lngRow + 1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).Insert
Range("B" & lngRow) = "=SUM(B" & lngStart & ":B" & lngRow - 1 & ")"
'Remark the below line if you want to convert the formulas to actual values.
'Range("B" & lngRow).Value = Range("B" & lngRow)
lngRow = lngRow + 1: lngStart = lngRow
End If
Loop Until Range("B" & lngRow) = ""
End Sub
If possible and practical, I would like to edit the above with additional VBA code using the respective “Subtotal” generated above to populate Column M (starting in row 2) with the following formula:
=(1-(b2/$b$13))/(b2/$b$13); =(1-(b3/$b$13))/(b3/$b$13); =(1-(b4/$b$13))/(b4/$b$13); (etc.)
[Note 1: Same formula calculation for cells (b2:b12) and for illustration purposes only, $b$13 = the cell location of the first Subtotal…. Then, repeat based on cell location of next Subtotal…. RE: All columns are “blank” in each Subtotal row except for Column B]
I am trying to automate current daily spreadsheet that usually has in excess of 1000 rows which doing manually, is obviously very time consuming…. I am new to VBA and any help would be greatly appreciated.
Thanks in advance,
KWL
Sub GroupInsertRowAndTotal()
Dim lngRow As Long, lngStart As Long
lngStart = 2: lngRow = lngStart
Do: lngRow = lngRow + 1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).Insert
Range("B" & lngRow) = "=SUM(B" & lngStart & ":B" & lngRow - 1 & ")"
'Remark the below line if you want to convert the formulas to actual values.
'Range("B" & lngRow).Value = Range("B" & lngRow)
lngRow = lngRow + 1: lngStart = lngRow
End If
Loop Until Range("B" & lngRow) = ""
End Sub
If possible and practical, I would like to edit the above with additional VBA code using the respective “Subtotal” generated above to populate Column M (starting in row 2) with the following formula:
=(1-(b2/$b$13))/(b2/$b$13); =(1-(b3/$b$13))/(b3/$b$13); =(1-(b4/$b$13))/(b4/$b$13); (etc.)
[Note 1: Same formula calculation for cells (b2:b12) and for illustration purposes only, $b$13 = the cell location of the first Subtotal…. Then, repeat based on cell location of next Subtotal…. RE: All columns are “blank” in each Subtotal row except for Column B]
I am trying to automate current daily spreadsheet that usually has in excess of 1000 rows which doing manually, is obviously very time consuming…. I am new to VBA and any help would be greatly appreciated.
Thanks in advance,
KWL
