Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message in macro to insert new row?
The following macro shows all the rows in a filtered list, inserts a row, and
then inserts a number in the first cell of the new row that is halfway between the numbers over and under it: Sub InsertRows() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub It works just fine. I'm trying to modify it, however, so that the new inserted row is gray in columns A-L. I've tried the following: Sub InsertGrayHead() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert With ActiveRow Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15 Pattern = xlSolid End With Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub Now I get a run-time error 424, saying that an object is required. Clearly I've screwed up on the new range reference ("With ActiveRow..."), but I don't know how to fix it. (I'm really new to all of this.) I'd really appreciate any help anyone can offer! Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message in macro to insert new row?
Wuddus, you have two issues in play he
1) Use the ActiveCell.Row object instead of the ActiveRow obejct (I could not find reference to ActiveRow). 2) There is no location Cells(0,1). Cells refers to cells collection of the ActiveSheet and it begins with Cells(1,1) (which is actually the cell "A1"). Range(Cells(ActivCell.Row, 1), Cells(ActiveCell.Row, 12)).Interior.ColorIndex = 15 Current Code: With ActiveRow Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15 Pattern = xlSolid End With New Code: Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12)).Interior.ColorIndex = 15 Pattern = xlSolid Hope this helps - let me know if you'd like more details. Regards, Bill "wuddus" wrote: The following macro shows all the rows in a filtered list, inserts a row, and then inserts a number in the first cell of the new row that is halfway between the numbers over and under it: Sub InsertRows() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub It works just fine. I'm trying to modify it, however, so that the new inserted row is gray in columns A-L. I've tried the following: Sub InsertGrayHead() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert With ActiveRow Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15 Pattern = xlSolid End With Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub Now I get a run-time error 424, saying that an object is required. Clearly I've screwed up on the new range reference ("With ActiveRow..."), but I don't know how to fix it. (I'm really new to all of this.) I'd really appreciate any help anyone can offer! Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message in macro to insert new row?
Bill:
That worked perfectly! It would clearly help if I actually used VBA instead of just inventing my own language. It's back to "Excel VBA for Dummies" for me! Thanks again! "Bill Pfister" wrote: Wuddus, you have two issues in play he 1) Use the ActiveCell.Row object instead of the ActiveRow obejct (I could not find reference to ActiveRow). 2) There is no location Cells(0,1). Cells refers to cells collection of the ActiveSheet and it begins with Cells(1,1) (which is actually the cell "A1"). Range(Cells(ActivCell.Row, 1), Cells(ActiveCell.Row, 12)).Interior.ColorIndex = 15 Current Code: With ActiveRow Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15 Pattern = xlSolid End With New Code: Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12)).Interior.ColorIndex = 15 Pattern = xlSolid Hope this helps - let me know if you'd like more details. Regards, Bill "wuddus" wrote: The following macro shows all the rows in a filtered list, inserts a row, and then inserts a number in the first cell of the new row that is halfway between the numbers over and under it: Sub InsertRows() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub It works just fine. I'm trying to modify it, however, so that the new inserted row is gray in columns A-L. I've tried the following: Sub InsertGrayHead() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveCell.EntireRow.Insert With ActiveRow Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15 Pattern = xlSolid End With Cells(ActiveCell.Row, 1).Value = _ (Cells(ActiveCell.Row - 1, 1).Value + _ Cells(ActiveCell.Row + 1, 1).Value) / 2 End Sub Now I get a run-time error 424, saying that an object is required. Clearly I've screwed up on the new range reference ("With ActiveRow..."), but I don't know how to fix it. (I'm really new to all of this.) I'd really appreciate any help anyone can offer! Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert a symbol | New Users to Excel | |||
Insert Row before Macro Button | Excel Discussion (Misc queries) | |||
How do I insert a prompt into an Excel macro? | Excel Discussion (Misc queries) | |||
Activate a macro to insert a row and copy the formuals from the rows above to the blank row | Excel Discussion (Misc queries) | |||
How do I insert the date using a macro | Excel Discussion (Misc queries) |