PDA

View Full Version : Object Required



dabearsrock
09-20-2011, 11:08 PM
I'm trying to figure out why I get the error referenced in the thread title. The function content works, I checked it already, so I must be doing something wrong in calling the function. Thanks.

Here is my code:

Function Insert_Row_If_Appropriate(Excel_Range As range)

If Count_UnBlank_Rows(Excel_Range) >= 3 Then
Excel_Range.Rows(Excel_Range.Rows.Count).EntireRow.Insert
End If

End Function

Sub knowledge_tree()

Dim Range_1 As range

Set Range_1 = range("Tree_Body")

Insert_Row_If_Appropriate (Range_1)

End Sub

mancubus
09-21-2011, 01:46 AM
try:

Insert_Row_If_Appropriate Range_1

dabearsrock
09-21-2011, 09:35 AM
Thanks so much, I had so much trouble finding a solution on google.

mancubus
09-21-2011, 10:03 AM
wellcome.

if Count_UnBlank_Rows(Excel_Range) is a UDF which returns number of populated cells (nonblanks), you may use built-in Application.CountA instead:

Function Insert_Row_If_Appropriate(Excel_Range As Range)

If Application.CountA(Excel_Range) >= 3 Then
Excel_Range.Rows(Excel_Range.Rows.Count).EntireRow.Insert
End If

End Function

Kenneth Hobs
09-21-2011, 11:27 AM
The other way to do it is with Call.
Call Insert_Row_If_Appropriate (Range_1)