Help with VBA code for Excel 2007

[KWL]

Looking for some help…. I am using the following macro to insert a blank row (to separate data into varying Group rows) and then, Subtotal Column B per each Group:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
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
<o> </o>
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:
<o> </o>
=(1-(b2/$b$13))/(b2/$b$13); =(1-(b3/$b$13))/(b3/$b$13); =(1-(b4/$b$13))/(b4/$b$13); (etc.)
<o> </o>
[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]
<o> </o>
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.
<o> </o>
Thanks in advance,
<o> </o>
KWL

 

[Kavy]

If I understand your problem, the below code may help, it just sets the value of a sell to whatever you want, in this case, i believe it would include your formula (ensure the quotations are included). It wouldn't drag your formula down, but with a loop it cool be done.

cells(row,col).value = "=(1-(b2/$b$13))/(b2/$b$13)"

where row and col are both numbers (i.e. for col A=1, b=2).

You can set the row and/or col to a variable like r and c, and have them rotate through with loops.

If you need to adjust the values of your formula with loops, i.e the "b2" in the above formula which has no "$" signs, replace the 2 with " & j & " where j is a variable like the r or c i had above.

For j = 2
cells(row,col).value = "=(1-(b" & j & "/$b$13))/(b" & j & "/$b$13)"
Next j

If your dealing with multiple worksheets you need to set your worksheet as

Dim ws1 as worksheet
set ws1 = "worksheetname"

ws1.cells(row,col).value = "bla"


Kavy

 

[AlphaFrog]

Hi and welcome.

Try something like this...

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)
    [COLOR="Red"]Range("M" & lngStart & ":M" & lngRow - 1).FormulaR1C1 = "=(1-(RC[-11]/R" & lngRow & "C2))/(RC[-11]/R" & lngRow & "C2)"[/COLOR]
    lngRow = lngRow + 1: lngStart = lngRow
    End If
    Loop Until Range("B" & lngRow) = ""
End Sub

 

[KWL]

Thank you both for your thoughful replies!

AlphaFrog.... Your suggestion worked to perfection.... You are a VBA programming genius! Thanks so very much for supplying the Code.

I've certainly got alot to learn!

Best regards,

KWL