Error 438 Object doesn't support this property or method 1

[vba317]

I am trying to develop a function that will automatically set the print area for an excel page. I keep on getting the above error. Any help is appreciated. Tom

The error is highlighted in blue

Public Function SetPrintArea()

    Dim rng As Excel.Range
    Dim LastRow As Integer
    LastRow = ActiveCell.SpecialCells(xlLastCell).Row
[Blue]    Set rng = goXl.Range("A1", goXl.UsedRange.SpecialCells(xlLastCell)) [/Blue]
    goXl.ActiveSheet.PageSetup.PrintArea = rng
   
    On Error GoTo 1
1:     Exit Function
     
    With goXl.ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = goXl.InchesToPoints(0.25)
        .RightMargin = goXl.InchesToPoints(0.25)
        .TopMargin = goXl.InchesToPoints(0.5)
        .BottomMargin = goXl.InchesToPoints(0.5)
        .HeaderMargin = goXl.InchesToPoints(0.5)
        .FooterMargin = goXl.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Function

 

[MajP]

It appears by context that gox1 is a workbook object. I would think you need to add the sheet reference before referring to the range property.

 

[vba317]

I changed the code to add the Active Sheet. Now I get an error 1004

Public goXl As Excel.Application 

LastRow = ActiveCell.SpecialCells(xlLastCell).Row
    'goXl.UsedRange.SpecialCells(xlLastCell)
    Set rng = goXl.ActiveSheet.Range(1, LastRow)
    goXl.ActiveSheet.PageSetup.PrintArea = rng

{/code]

 

[Andrzejek]

In your original post you were going after A1 and down
So:

Public goXl As Excel.Application 

Set rng = goXl.ActiveSheet[blue].Range("A1", ActiveCell.SpecialCells(xlLastCell))
[/blue]
goXl.ActiveSheet.PageSetup.PrintArea = rng

Have fun.

---- Andy

 

[vba317]

Andy,
I appreciate your input, now I am getting a different error, Runtime error 1004. Unable to set the print area property of the pagesetup class.
The following code is highlighted

goXl.ActiveSheet.PageSetup.PrintArea = rng

 

[MajP]

printarea is a string property and you are trying to make it equal a range object.

 

[MajP]

Probably need the address

http://msdn.microsoft.com/en-us/library/office/ff837625.aspx

 

[Deniall]

This is way outside my comfort zone, but is your problem somehow related to the fact that you are trying to do in a function what I would usually do in a sub?

 

[SkipVought]

EACH & EVERY reference to ActiveSheet needs the Excel Application Object Reference!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

MaJp,

I thought this might be the problem. I have written functions to get the last col and row.

This works I appreciate you helping go in the right direction.
Current code

Public Function SetPrintArea(strSheet As String)

    Dim rng As Excel.Range
    Dim LastRow As Integer
    Dim iBotRow As Integer
    Dim iLastCol As Integer
    Dim strLastCol As String
    
    Call xlCalcBotRow(iBotRow)
    iBotRow = iBotRow + 1
    Call xlCalcLastCol(iLastCol, intRpt)
    strLastCol = (ConvColLet(iLastCol))
    Worksheets(strSheet).PageSetup.PrintArea = "$A$1" & ":" & "$" & (strLastCol) & "$" & iBotRow

modules
Public Function xlCalcLastCol(iLastCol As Integer, intRpt As Integer)
' ***************************************************************************
' *** THIS FUNCTION CALCULATES THE LAST COLUMN OF DATA  *********************
' ***************************************************************************
'Call xlCalcLastCol(iLastCol)
    Dim iRow As Integer
    iLastCol = 0
    'Calculate Last Column
    If intRpt < 3 Then iRow = 2
    If intRpt > 2 Then iRow = 3
    iLastCol = Cells(iRow, Columns.Count).End(xlToLeft).Column
End Function

Public Function xlCalcBotRow(iBotRow As Integer)
' ***************************************************************************
' *** THIS FUNCTION CALCULATES THE BOTTOM ROW OF DATA  **********************
' ***************************************************************************
'Call xlCalcBotRow(iBotRow)
'Calculate Bottom Row
    iBotRow = 0
    iBotRow = Cells(Rows.Count, "A").End(xlUp).Row

End Function