macro to insert formulas in blank rows

L

luke1438

Board Regular
Joined
Nov 1, 2004
Messages
156
I am trying to insert several formulas into blank rows to calculate data in rows above the blank rows. Example: I have data in columns (the amount of rows varies and are seperated by 3 blank rows) and need to put a formula in the first blank row after each varying series of data that calculates the cells above all the way to the blank row above.

Blank Row
data
data
data
INSERT FORMULA (to sum above 3 rows)
Blank Row
Blank Row
data
data
data
data
data
data
Insert Formula (to sum above 6 rows)
Blank Row
Blank Row
data
data
Insert Formula (to sum above 2 rows)
Blank Row
Blank Row
data
etc.....

I have been trying different types of code and I am getting formulas in all three blank rows and summing everthing except the top data row. Any help or suggestions woul dbe greatly appreciated.

Thanks
Luke
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Try this:
Code: 
Option Explicit

Sub StaggeredSummation()
'JBeaucaire (11/11/2009)
'Add sums to ranges of values
Dim FR As Long, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
FR = 1

    For i = 2 To LR
        If i = LR Then
            Cells(i + 1, "A").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i & "C)"
        ElseIf Cells(i, "A") = "" And Cells(i + 2, "A") = "" Then
            Cells(i, "A").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
            FR = i + 3
            i = i + 3
        End If
    Next i
End Sub


=========
I also tried to use the .Areas property to reduce the loops to only the number of unique areas, but couldn't figure out the syntax to add the sum to the bottom of each area.

Code: 
Sub StaggeredSumAreas()
Dim LR As Long, i As Long, Rng As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:G" & LR).SpecialCells(xlCellTypeConstants)

    For i = 1 To Rng.Areas.Count
        MsgBox = Application.WorksheetFunction.Sum(Rng.Areas(i))
    Next i
End Sub

If someone knows how to change the MSGBOX to the cell address just below each area, I'd love the assist.
 
Last edited:
Upvote 0
JBeaucaire,

Thanks, it works very well. I made some modifications so that different formulas go into several different cells - see code below. If I wanted to add another set of formulas into the next row below this one what would I have to do?

Code:
Option Explicit

Sub StaggeredSummation()
'JBeaucaire (11/11/2009)
'Add sums to ranges of values
Dim FR As Long, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
FR = 1

For i = 2 To LR
If i = LR Then
Cells(i + 1, "A").FormulaR1C1 = "=COUNTA(R" & FR & "C:R" & i & "C)"
ElseIf Cells(i, "A") = "" And Cells(i + 2, "A") = "" Then
Cells(i, "A").FormulaR1C1 = "=COUNTA(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "J").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "K").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "L").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "M").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "N").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "O").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "P").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "Q").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "R").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "S").FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "T").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "U").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "V").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "W").FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "X").FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "Y").FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
Cells(i, "Z").FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
FR = i + 3
i = i + 3
End If
Next i
End Sub

Thanks
Luke
 
Upvote 0
This combines the like operations into single commands:
Code: 
Sub StaggeredSummation()
'JBeaucaire (11/11/2009)
'Add sums to ranges of values
Dim FR As Long, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
FR = 1

    For i = 2 To LR
        If i = LR Then
            Cells(i + 1, "A").FormulaR1C1 = "=COUNTA(R" & FR & "C:R" & i & "C)"
        ElseIf Cells(i, "A") = "" And Cells(i + 2, "A") = "" Then
            Cells(i, "A").FormulaR1C1 = "=COUNTA(R" & FR & "C:R" & i - 1 & "C)"
            Range(Cells(i, "J"), Cells(i, "W")).FormulaR1C1 = "=SUM(R" & FR & "C:R" & i - 1 & "C)"
            Cells(i, "S").FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
            Range(Cells(i, "X"), Cells(i, "Z")).FormulaR1C1 = "=AVERAGE(R" & FR & "C:R" & i - 1 & "C)"
            FR = i + 3
            i = i + 3
        End If
    Next i
End Sub

Click on QUOTE in one my posts and you'll see the proper syntax for wrapping your code in [code] and [/code] tags.
 
Upvote 0
You must log in or register to reply here.

Similar threads

DTMnBHM
  • Question
Insert blank row and copy row to new blank row
2
Replies
13
Views
531
Coyotex3
C
theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
191
MARK858
MARK858
E
  • Question
Subtotal values and then hide rows
Replies
3
Views
115
Peter_SSs
Peter_SSs
S
  • Question
VBA Code- finding #Spill and moving the formula to the next column
Replies
0
Views
412
stromnoexcel
S
Keebler
  • Solved
clean code
5 6 7
Replies
66
Views
1K
Keebler
Keebler

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top