1004 : application-defined or object-defined error in excel VBA

Question

0.00/5 (No votes)

See more:

I have very big Macro code which will do one of the below functionalities.
We have templates which will describe how the report to be generated and the below code snippet it is getting the error. The functionality which it is doing is based on the template definition it will fetch the data and paste it into new sheet.

With objCurrentWorksheet
            If intTall > 1 Then
                ActiveWindow.View = xlPageBreakPreview
                    i = 1
                    .PageSetup.FitToPagesTall = False
                    .ResetAllPageBreaks
                    Do While i <= .HPageBreaks.Count
                        Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)
                        Do Until objCell.Height < 15
                            Set objCell = .Range("B" & objCell.Row - 1)
                        Loop
                        Set objCell = .Range("B" & objCell.Row + 1)
                        >>> It is throwing the error here
                        .HPageBreaks.Add Before:=objCell
                        i = i + 1
                    Loop
                ActiveWindow.View = xlNormalView
            End If
'            End If
        End With

    finishTemplate = True
    Exit Function

What I have tried:

I have tried to modify the template definitions but it hasn't worked out

Posted 18-Feb-18 22:38pm

Member 13638770

Updated 19-Feb-18 9:38am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-02-18T23:26:00

Solution 1

It looks like you have run off the end of the HPageBreaks collection with

VB

Do While i <= .HPageBreaks.Count

You should start traversing the collection from index 0 not 1 and stop before i equals the count.
Alternatively, use a for each construct e.g.

VB

Dim pb As HPageBreak
For Each pb in objCurrentWorksheet.HPageBreaks 
...

Posted 18-Feb-18 23:26pm

CHill60

Comments

Member 13638770 19-Feb-18 7:06am

Thanks @CHill60 One other concern that after modfying as you suggested im getting this error 9: subscript out of range. Am i missing any thing here?

CHill60 19-Feb-18 7:35am

On what line?

Member 13638770 19-Feb-18 7:40am

On the next line .HPageBreaks.Add Before:=objCell

Member 13638770 19-Feb-18 7:42am

Sorry the error is throwing on
Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)

Member 13638770 19-Feb-18 8:27am

Am i missing anything, Because still im getting the same error 9:Subscript Out Of Range :(

Maciej Los · Answer 2 · 2018-02-19T05:27:00

Member 13638770[^] wrote:

Sorry the error is throwing on

VB
Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)

First of all, please, read these:
You receive a "Subscript out of range" error message when you use HPageBreaks or VPageBreaks.Location in Excel[^]
Subscript out of range (Error 9)[^]

Well, you should avoid setting reference to the cell by using PageBreaks. If you trying to set page breaks, use custom method (custom logic), BUT you should clear PageBreaks settings first.