Macro for blank cells

[Guest]

I have some code (see below) that starts at cell A8 and is intended to look
DOWN until it finds a row with a blank cell in column A and then continues on
with the IF statement you see in the code. However, the CELLS.find....line
looks to the right and finds a blank cell (not suppose to do this) and then
the IF statement executes and everything goes down hill from there. Any ideas?



Do

BEGCELL = ActiveCell.Offset(1, 0).Row

Cells.Find("", AFTER:=ActiveCell).Activate

ENDCELL = ActiveCell.Offset(-1, 0).Row

If ActiveCell.Value = "" Then
Application.ActiveCell.Offset(0, 11).Formula = "=SUBTOTAL(9,L" &
BEGCELL & ":L" & ENDCELL & ")"
ActiveCell.Offset(0, 11).Copy
Range(ActiveCell.Offset(0, 10), ActiveCell.Offset(0,
14)).PasteSpecial xlPasteAll
Application.ActiveCell.Offset(0, 25).PasteSpecial
xlPasteAllExceptBorders
ActiveCell.Offset(0, -35).Select

If Mid(ActiveCell.Offset(1, 0).Value, 1, 12) =
"Grand totals" Then Exit Do

End If

ActiveCell.Offset(1, 0).Select

Loop

Thanks in advance,

Darryl

 

[Tom Ogilvy]

Range("A1").Select
Columns(1).Find("", AFTER:=ActiveCell).Activate

I haven't looked at the rest of your code.

 

[Guest]

Thank you Tom. One more thing if you don't mind...If you look at my code and
see where I am doing my Subtotal routine it is not doing it correctly. It
leaves off the first line of each section to total.

 

[Tom Ogilvy]

Range("A2").Select
Do

' BEGCELL = ActiveCell.Offset(1, 0).Row
BEGCELL = ActiveCell

Columns(1).Find("", AFTER:=ActiveCell).Activate

ENDCELL = ActiveCell.Offset(-1, 0).Row

If ActiveCell.Value = "" Then
Application.ActiveCell.Offset(0, 11).Formula = "=SUBTOTAL(9,L" &
BEGCELL & ":L" & ENDCELL & ")"
ActiveCell.Offset(0, 11).Copy
Range(ActiveCell.Offset(0, 10), ActiveCell.Offset(0,
14)).PasteSpecial xlPasteAll
Application.ActiveCell.Offset(0, 25).PasteSpecial
xlPasteAllExceptBorders
ActiveCell.Offset(0, -35).Select

If Mid(ActiveCell.Offset(1, 0).Value, 1, 12) =
"Grand totals" Then Exit Do

End If

ActiveCell.Offset(1, 0).Select

Loop

You move off the subtotal cell in the last executable line of the loop, so
you don't need to do it again at the top (with BEGCELL)