Macro for blank cells
 

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

Macro for blank cells
 

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

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

--
Regards,
Tom Ogilvy


"Darryl" wrote in message
...
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

Macro for blank cells
 

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" wrote:

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

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

--
Regards,
Tom Ogilvy


"Darryl" wrote in message
...
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

Macro for blank cells
 

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)

--
Regards,
Tom Ogilvy


"Darryl" wrote in message
...
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" wrote:

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

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

--
Regards,
Tom Ogilvy


"Darryl" wrote in message
...
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