Re: Inserting blank rows plus using Sum

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Try this...
It will inserst a SUM formula
Sub insert_sum_values()
Dim sum_of_range, tmp

For i = 2 To 8

If i = 2 Or i = 6 Or i = 7 Or i = 8 Then
'column numbers where sums required
Cells(3, i).Select
'first cell at top of range to be summed

Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
tmp = ActiveCell.Value
If tmp <> "" Then
sum_of_range = "=SUM(" & Selection.Address & ")"
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.End(xlDown).Offset(2, 0).Select
Else
sum_of_range = ""
End If
Loop Until sum_of_range = ""
End If
Next i
End Sub

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Bill95051" wrote:



"AltaEgo" wrote:

It may be possible to do this more efficiently but the sub below will insert
totals per your example.

Call the Sub after you insert your two blank rows.

I did not develop a routine to check that there are two blank rows between
sections so be careful to call it once only. Calling it twice or more does
modify existing data but could cause confusion.

Modified substantially from code located at
http://www.mrexcel.com/archive/VBA/4272.html


Thanks. I took your macro and tweaked some more. Here is what came out.
I changed the variable i to row and the Sub-sub's variable to col (they
conflicted with each other and also made it more readable). Passed the row
info to the Sub-sub. Had to change ActiveCell to ...End(xlup) instead of
down and trimmed out a few extra lines. Still have a problem with accurate
Summing from group to group. Somehow the Sum function still gets the wrong
column size. The original macro would do the first change in symbol, but
then ran to the bottom and Summed the whole remaining column.

Sub SumAndSeparate()
StartRow = 3 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

row = StartRow + 1
While Cells(row, DataColumn) <> ""

If Cells(row, DataColumn) <> Cells(row - 1, DataColumn) Then
Cells(row, DataColumn).EntireRow.Insert
Cells(row, DataColumn).EntireRow.Insert
SumValues (row)
row = row + 2
End If
row = row + 1
Wend
End Sub


Sub SumValues(row)
Dim sum_of_range, tmp

For col = 2 To 8

If col = 2 Or col = 6 Or col = 7 Or col = 8 Then
'column numbers where sums required
Cells(row, col).Select
'first cell at top of range to be summed

tmp = ActiveCell.Value
If ActiveCell.Value() = None Then
Range(ActiveCell.Offset(-2), ActiveCell.End(xlUp)).Select
' Range(ActiveCell, ActiveCell.End(xlUp)).Select

sum_of_range = Application.WorksheetFunction.Sum(Selection)

ActiveCell.Offset(2, 0).Value = sum_of_range


Else
sum_of_range = ""

End If
End If

' Range(ActiveCell.Offset(1)).Select
Next col
Cells(row + 2, col - 7).Select

End Sub

Thanks for the idea
Bill
.

Relevant Pages

  • Re: Help - Procedure expects parameter - Its There!!!
    ... Public Sub New ... Private Sub btnLoad_Click(ByVal sender As System.Object, ... Dim JobNumber As DataColumn = Me.tbl010Job.Columns.Add("JobNumber", ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: error when adding column to datatable: There is no row at posi
    ... Private Sub cmdSetup_Click(ByVal sender As System.Object, ... Dim myDataColumn As DataColumn ... Dim dataview As New DataView ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: error when adding column to datatable: There is no row at posi
    ... Private Sub cmdSetup_Click(ByVal sender As System.Object, ... Dim myDataColumn As DataColumn ... Dim dataview As New DataView ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Excel Row/Column Merge
    ... will run until it has finshed the duplications then calls the deletedupes. ... Sub t ... Dim i As Long, nr As Long, col As Integer, nc As Integer ... Dim c, d As String, tmp, count As Long, max As Long ...
    (microsoft.public.excel)
  • Re: Sort an array
    ... in your sort routine just use InstrRev to ... Sub BubbleSortAs String) ... Dim i As Long, j As Long ... Dim Tmp As String ...
    (microsoft.public.vb.general.discussion)