Application-Defined Error 1004
 

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

Application-Defined Error 1004
 

Maybe a missing period:

Set rngLast = Cells(intLastRow, fintHeaderColumn())
could be:
Set rngLast = .Cells(intLastRow, fintHeaderColumn())

(but fintheadercolumn() could be trouble, too <vbg.)

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

--

Dave Peterson

Application-Defined Error 1004
 

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

Application-Defined Error 1004
 

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