CODEWITHSUNDEEP
excelvbainsertruntime-error
Philip Day
Philip Day

Reputation: 69

Macro to insert rows and fill is failing

I've written the following macro to insert a specified number of rows into a table, and then to filldown, to re-populate the table with the correct forumulas.

I don't know why this is failing with an error at ActiveSheet.Rows(r).Insert with runtime error 1004. Insert method of Range class failed.

Could you help me out?

Sub SetKPIDuration()

Dim Duration As Integer, i As Integer, r As Integer

Duration = InputBox("Enter number of week for KPI to run (min 18)", 
"Duration of KPI", 18)

Select Case True
    Case Duration < 10
        Duration = 18
        GoTo IncreaseKPI
    Case Duration < Application.WorksheetFunction.Max(Range("A7:A150"))
        GoTo ReduceKPI
    Case Else
        GoTo IncreaseKPI
End Select

ReduceKPI:

Rows((Duration + 7) & ":150").Clear
Exit Sub

IncreaseKPI:
Application.ScreenUpdating = False
i = Application.WorksheetFunction.Max(Range("A7:A150"))
r = i + 7

While i < Duration

    ActiveSheet.Rows(r).insert

Wend

Range("A" & (r - 1) & ":" & "M" & (r + i)).filldown


Application.CutCopyMode = False


ScreenUpdating = True

End Sub

Upvotes: 1

Views: 193

Answers (2)

Justin
Justin

Reputation: 65

It looks like your line below might not be returning the result you want:

i = Application.WorksheetFunction.Max(Range("A7:A150"))

if i is less than 18, you'll get an infinite loop here:

While i < Duration

    ActiveSheet.Rows(r).insert

Wend

...which eventually results in your 1004 error

Upvotes: 1

Curtis000
Curtis000

Reputation: 141

Try fully qualifying your ranges with full workbooks.worksheets.range paths.

Upvotes: 1

Related Questions