Macro malfuntion

  • Thread starter Thread starter AshMorK
  • Start date Start date
A

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!
 
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
 
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.
 
Thanks Mike!!

Now it runs OK.

Mike H said:
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
Click to expand...
 
Back
Top