Help determining sum range to insert into formula

I am trying to dynamically sum a range of cells based on certain critera via a macro.

Here is my current vba script:

For i = 3 To lastRow
For j = 10 To (Range("H1").Value + 12)
If Range("B" & i) <> "" Then
Cells(i, j).Formula = "=IF(SUM(" & ActiveCell.Offset(1, 0) & ")>=$B$" & i & ", _
SUM(" & ActiveCell.Offset(1, 0) & ")-$B$" & i & ",0)"
End If
Next j
Next i

This is placing the formula in the correct cells for me, but I cannot get the sum to identify the correct range.

In all instances, the range to be summed will be the cell immediately below the cell this formula is pasted into and extends until a blank cell is reached. The sum range will vary from 1 cell to many cells.

The data looks similar to this



5 0 3 0 0 <----Sum Formula 1
1 0 0 0 0 <----Start Sum Range 1
0 0 2 0 0
4 0 1 0 0
0 0 0 0 0 <----End Sum Range 1

7 0 0 9 0 <----Sum Formula 2
7 0 0 3 0 <----Start Sum Range 2
0 0 0 3 0
1 0 0 0 0
1 0 0 3 0 <----End Sum Range 2

If anyone has an idea of how I can write this part of the vba, I could certainly use the help.

When using ranges, offsets etc to get a cell address which is entered into a worksheet formula you need to use .address

Try

Cells(i, j).Value = "=IF(SUM(" & ActiveCell.Offset(1, 0).Address & ")>=$B$" & i & _
", SUM(" & ActiveCell.Offset(1, 0) & ")-$B$" & i & ",0)"

Thank you for the tip regarding using the "Address" command.

I still cannot get the correct range of cells to populate my sum command however.

Is there a way, when using the offset command, to find the address of the next blank cell as it looks down the column?

For instance, I would like my sum range to be (J6:J29). Cell J30 is a blank cell. Therefore, if I can write a bit of code that returns the address of the blank cell (J30), I think this would work.

Heres a few different ways to find blank cells

The one in red would suit your needs - This will give the last row before a blank cell.
Change A1 to ActiveCell.Offset(1, 0).Address or any other cell address

Please Login or Register  to view this content.

Thanks for the help Mudraker

I had to wind up doing something slightly different...and while it feels clumsy...it seems to work just fine.

Here is what I did...

For i = 3 To lastRow
For j = 10 To (Range("H1").Value + 10)
If Range("B" & i) <> "" Then
Cells(i, j).Formula = "=IF(SUM(" & Cells(i, j).Offset(1, 0).Address & ":" & Cells(i + 1, j).End(xlDown).Address & ")>=$B$" & i & ",SUM(" & Cells(i, j).Offset(1, 0).Address & ":" & Cells(i + 1, j).End(xlDown).Address & ")-$B$" & i & ",0)"
Cells(i, j).Font.Bold = True
If Cells(i, j).Value = 0 Then
Cells(i, j).Font.Color = RGB(0, 128, 0)
End If
If Cells(i, j).Value > 0 Then
Cells(i, j).Font.Color = RGB(255, 0, 0)
End If
End If
Next j
Next i

As you can see, I added in a few formatting enhancements too. Thanks for pointing me in the right direction!

Glad to hear you got it working.

Whilst it the code is not the most elegant it works and that is the most important bit. There is not much I could offer to improve on it.