I'm trying to write code that will scan across a row, find
blank cells, and sum the fill area above it. I can't seem
to record a macro to do this though. When I use relative
referencing I get the following:

ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-2, 0).Range("A1:A4").Select
ActiveCell.Offset(1, 0).Range("A1").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(-3, 0).Range("A1:A4").Select
ActiveCell.Offset(-1, 0).Range("A1").Activate

Obviously this won't work because it references a set
number of rows. Does anyone know a good way to handle
this? The number of rows can vary from one to over 70.

Just for kicks, here's the rest fo my current code, which
hasn't been tested yet.

Sub Tester()

Dim cell As Range
For Each cell In Range("C2:C100")
If Right(cell.Offset(0, -2), 5) = "TOTAL" Then
ActiveCell.Rows("1:1").EntireRow.Select
For Each cell In Selection
If Right(ActiveCell.Value, 2) 0 Then
ActiveCell.FormulaR1C1 = ""
Else
If ActiveCell.Value = "" Then

Still obviously in progress, but I thought I'd post it
because I'm still nto too sure of myself.

Thanks in advance.

There's a worksheet function called "SUMIF". The syntax is
SUMIF(A1:A10, "0", B1:B10). What this does is looks in A1-A10 and if
the values are 0, sums the corresponding value in column B. If the
third argument (B1:B10) is left off, the summing is done with the
values in column A. Do a help on SUMIF for more info.

Kin

On Wed, 20 Aug 2003 07:37:03 -0700, "Chris M."
wrotE:

I'm trying to write code that will scan across a row, find
blank cells, and sum the fill area above it. I can't seem
to record a macro to do this though. When I use relative
referencing I get the following:

Chris

I'm not quite sure what you're trying to do, but maybe this example will get
you started.

Sub MakeSums()

Dim FndRng As Range
Dim sh As Worksheet
Dim cell As Range
Dim SumRng As Range

Set sh = ThisWorkbook.Sheets(1)

'Find the cell with TOTAL in it
Set FndRng = sh.Range("C1:C100").Find("TOTAL", , , xlWhole)

'If TOTAL was found
If Not FndRng Is Nothing Then

'Loop through the cells in that row
For Each cell In Intersect(FndRng.EntireRow, sh.UsedRange).Cells

'If a blank cell is found
If IsEmpty(cell) Then

'Set the range to be summed
Set SumRng = sh.Range(cell.Offset(-1, 0), _
cell.Offset(-1, 0).End(xlUp))

'Put a sum formula in the cell using the Address
'of the sumrng
cell.Formula = "=SUM(" & SumRng.Address & ")"
End If
Next cell
End If

End Sub
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Chris M." wrote in message
...
I'm trying to write code that will scan across a row, find
blank cells, and sum the fill area above it. I can't seem
to record a macro to do this though. When I use relative
referencing I get the following:

ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-2, 0).Range("A1:A4").Select
ActiveCell.Offset(1, 0).Range("A1").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(-3, 0).Range("A1:A4").Select
ActiveCell.Offset(-1, 0).Range("A1").Activate

Obviously this won't work because it references a set
number of rows. Does anyone know a good way to handle
this? The number of rows can vary from one to over 70.

Just for kicks, here's the rest fo my current code, which
hasn't been tested yet.

Sub Tester()

Dim cell As Range
For Each cell In Range("C2:C100")
If Right(cell.Offset(0, -2), 5) = "TOTAL" Then
ActiveCell.Rows("1:1").EntireRow.Select
For Each cell In Selection
If Right(ActiveCell.Value, 2) 0 Then
ActiveCell.FormulaR1C1 = ""
Else
If ActiveCell.Value = "" Then

Still obviously in progress, but I thought I'd post it
because I'm still nto too sure of myself.

Thanks in advance.