Error message in macro to insert new row?

[Guest]

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!

 

[Guest]

Wuddus, you have two issues in play here:
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

 

[Guest]

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!