Prev: Excel will not open in default file location
Next: macro - copy/add to another sheet
From: Randy on 9 Mar 2010 13:36
Why won't the following macro work to create a page break - in particular it
is giving me an error at the InStr line

Sub b8Pagebrk()
Dim theRange As Range
Dim lastrow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") > 0 Then
Cells(x + 1, 1).PageBreak = xlManual

End If
Next
End Sub



--
Randy
From: Dave Peterson on 9 Mar 2010 13:49
The only time I've seen this kind of line:
If InStr(1, Cells(x, 1), "Total") > 0 Then
cause an error is when there's an error in that cell.

I'd try:
If InStr(1, Cells(x, 1).Text, "Total") > 0 Then

I've seen it when x isn't a valid row number, but that doesn't look like the
problem here.

Randy wrote:
>
> Why won't the following macro work to create a page break - in particular it
> is giving me an error at the InStr line
>
> Sub b8Pagebrk()
> Dim theRange As Range
> Dim lastrow&, firstRow&, x&
> Set theRange = ActiveSheet.UsedRange
> lastrow = theRange.Cells(theRange.Cells.Count).Row
> firstRow = theRange.Cells(1).Row
> For x = lastrow To firstRow Step -1
> If InStr(1, Cells(x, 1), "Total") > 0 Then
> Cells(x + 1, 1).PageBreak = xlManual
>
> End If
> Next
> End Sub
>
> --
> Randy

--

Dave Peterson
 | 
Pages: 1
Prev: Excel will not open in default file location
Next: macro - copy/add to another sheet