Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range for Sum Formula
One way if all those numbers are values--not formulas:
Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range Dim myCell As Range Dim wks As Worksheet Dim myFormula As String Set wks = ActiveSheet With wks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No constants in that range" Exit Sub End If For Each myArea In myRng.Areas With myArea Set myCell = .Cells(.Cells.Count).Offset(1, 0) If .Cells.Count = 1 Then myFormula = "=" & myArea.Address(0, 0) Else myFormula = "=sum(" & myArea.Address(0, 0) & ")" End If myCell.Formula = myFormula End With Next myArea End With End Sub Paige wrote: I have a column of numbers and want to add subtotals at certain points. Because of the way the file is set up and how it is used, I can't use the normal subtotal function. Here is how the data is set up: Col A Row 22 Header Row 23 34 Row 24 2 Row 25 9 blank row (or rows) Row 27 1 blank rows (or rows) etc. The # of blank rows varies, as does the number of rows to add. I know how to find the cell where I want the formula to go, but cannot figure out how to write the formula to put there. Example above - formula goes in Cell A26; it should add starting the cell above until it hits either a blank cell or the word 'header'. In this case the result should be =sum(A25:A23). Cell A28 should have =sum(A27:A27). Am desperate for help on this; any input would be appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Range in Formula | Excel Discussion (Misc queries) | |||
Sum Formula with variable range | Excel Discussion (Misc queries) | |||
Formula based on variable range | Excel Worksheet Functions | |||
Variable Determines Number of Cell in Formula Range | Excel Worksheet Functions | |||
Code to copy formula to variable range | Excel Programming |