Thank you, Dave & Jim for your responses. I made the change you suggested
and it appears to have fixed the problem. Interestingly, the original code
was working in almost every instance, it failed only rarely, and moreover,
never on my machine, just on a colleague's (even working on the same file).
I had not figured out why, but having a solution, aren't going to bother. Do
you have any ideas?
The code for fintHeaderColumn is below. I can't see how it could return
anything but an integer between 1 and 25.
Thanks again.
Sprinks
Function fintHeaderColumn() As Integer
' Finds the right-hand most column of the worksheet by looking for company
banner in the first row
' F is the default column
On Error GoTo ErrHandler
Const cstrHeaderText = "B E C K E R & F R O N D O R F"
Const cintRow = 1
Const cintDefaultColumn = 6
Const cintMaxColumn = 25
Dim intCurCol As Integer
Dim rng As Range
fintHeaderColumn = cintDefaultColumn
For intCurCol = 1 To cintMaxColumn
Set rng = Cells(cintRow, intCurCol)
If rng.Value = cstrHeaderText Then
fintHeaderColumn = intCurCol
Exit For
End If
Next intCurCol
ErrExit:
Exit Function
ErrHandler:
MsgBox "There has been the following error. Please contact the macro
administrator." & _
vbCrLf & vbCrLf & Err.Number & vbCrLf & " " & Err.Description
Resume ErrExit
End Function
"Jim Thomlinson" wrote:
> Two things with your code. First off you probably (not definite but that was
> my guess) want to set rngLast based on the wrksht object so you need a dot in
> front of Cells(intLastRow, fintHeaderColumn())
>
> Set rngLast = .Cells(intLastRow, fintHeaderColumn())
>
> Now what does your function fintHeaderColumn() return??? It must be a number
> between 1 and 256. Anything else will return the error you describe...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Sprinks" wrote:
>
> > In a macro that checks for a number of errors and sets the zoom, print range,
> > and page breaks for each worksheet, I'm getting this error on the Set rngLast
> > line:
> >
> > For Each wrksht In ActiveWorkbook.Worksheets
> >
> > With wrksht
> > .Activate
> > intLastRow = GetLastRow()
> >
> > If intLastRow <> 0 Then
> >
> > ' Reset zoom and view
> > With ActiveWindow
> > .View = xlNormalView
> > .Zoom = intZoom
> > End With
> >
> > ' Assign print range and set page breaks
> > Set rngLast = Cells(intLastRow, fintHeaderColumn())
> > .PageSetup.PrintArea = "$A$1:" & rngLast.Address
> > Call SetPageBreaks(intLastRow)
> >
> > Does anyone know why?
> >
> > Sprinks
|
Similar Threads
