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!
|