Macro malfuntion

[AshMorK]

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 Before:=Range("b" &
rng.Row).EntireRow.Offset(-2)
End If
End If
Next rng
End Sub

Thanks in Advance!

 

[Mike H]

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

 

[John Bundy]

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.

 

[Don Guillett]

Perhaps you have "Cuenta" in row 2 or 3

 

[AshMorK]

Thanks Mike!!

Now it runs OK.

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