| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| i need a macro to read blank cells | Excel Discussion (Misc queries) | |||
| macro needed for non-blank cells | Excel Worksheet Functions | |||
| Macro to Delete blank cells | Excel Worksheet Functions | |||
| Using a Macro to paste into Blank Cells | Excel Worksheet Functions | |||
| A macro to fill in the blank cells | Excel Programming | |||
Linear Mode
