Excel Need VBA help for Excel Spreadsheet


KWL

Joined
Apr 17, 2011
Messages
2
Reaction score
0
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top