Macro malfuntion
 

Hi All,

I'm using the following code to insert a hpagebreak everytime a specific
value ("Cuenta") is found. The problem is that although it works perfectly
in a simple test workbook, it returns the '1004' Runtime error (Application
or Objet defined error) when I try to use it in a more complex workbook.

Sub addhpb()

Dim rng As Range

Worksheets(1).ResetAllPageBreaks

For Each rng In ActiveSheet.Range("b1:b82")
If Not rng.Row = 1 Then
If rng.Value = "Cuenta" Then

ActiveSheet.HPageBreaks.Add Befo=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!

Macro malfuntion
 

You will get that error if it encounters Cuenta in row 2, try this
If Not rng.Row <= 2 Then

You could do away with the entire if/end if loop by changing you range to
start in B3 in the other nested loop

Mike

"AshMorK" wrote:

Hi All,

I'm using the following code to insert a hpagebreak everytime a specific
value ("Cuenta") is found. The problem is that although it works perfectly
in a simple test workbook, it returns the '1004' Runtime error (Application
or Objet defined error) when I try to use it in a more complex workbook.

Sub addhpb()

Dim rng As Range

Worksheets(1).ResetAllPageBreaks

For Each rng In ActiveSheet.Range("b1:b82")
If Not rng.Row = 1 Then
If rng.Value = "Cuenta" Then

ActiveSheet.HPageBreaks.Add Befo=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!

Macro malfuntion
 

You are starting at the first row with code to go back 2, you need to handle
what to do if it is encountered in the first two rows.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"AshMorK" wrote:

Hi All,

I'm using the following code to insert a hpagebreak everytime a specific
value ("Cuenta") is found. The problem is that although it works perfectly
in a simple test workbook, it returns the '1004' Runtime error (Application
or Objet defined error) when I try to use it in a more complex workbook.

Sub addhpb()

Dim rng As Range

Worksheets(1).ResetAllPageBreaks

For Each rng In ActiveSheet.Range("b1:b82")
If Not rng.Row = 1 Then
If rng.Value = "Cuenta" Then

ActiveSheet.HPageBreaks.Add Befo=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!

Macro malfuntion
 

Perhaps you have "Cuenta" in row 2 or 3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AshMorK" wrote in message
...
Hi All,

I'm using the following code to insert a hpagebreak everytime a specific
value ("Cuenta") is found. The problem is that although it works
perfectly
in a simple test workbook, it returns the '1004' Runtime error
(Application
or Objet defined error) when I try to use it in a more complex workbook.

Sub addhpb()

Dim rng As Range

Worksheets(1).ResetAllPageBreaks

For Each rng In ActiveSheet.Range("b1:b82")
If Not rng.Row = 1 Then
If rng.Value = "Cuenta" Then

ActiveSheet.HPageBreaks.Add Befo=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!

Macro malfuntion
 

Thanks Mike!!

Now it runs OK.

"Mike H" wrote:

You will get that error if it encounters Cuenta in row 2, try this
If Not rng.Row <= 2 Then

You could do away with the entire if/end if loop by changing you range to
start in B3 in the other nested loop

Mike

"AshMorK" wrote:

Hi All,

I'm using the following code to insert a hpagebreak everytime a specific
value ("Cuenta") is found. The problem is that although it works perfectly
in a simple test workbook, it returns the '1004' Runtime error (Application
or Objet defined error) when I try to use it in a more complex workbook.

Sub addhpb()

Dim rng As Range

Worksheets(1).ResetAllPageBreaks

For Each rng In ActiveSheet.Range("b1:b82")
If Not rng.Row = 1 Then
If rng.Value = "Cuenta" Then

ActiveSheet.HPageBreaks.Add Befo=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!