+ Reply to Thread
Results 1 to 2 of 2

Range is not being picked up

  1. #1
    Registered User
    Join Date
    05-04-2004
    Posts
    15

    Range is not being picked up

    Please take a look at my code, I am trying to pick up the range and print it, depending on the analysis years.

    Dim area As String
    Dim startrow(1) As Integer
    Dim startcol(1) As Integer
    Dim endrow(1) As Integer
    Dim endcol(1) As Integer
    Dim vern_startrow(1) As Integer
    Dim vern_startcol(1) As Integer
    Dim vern_endrow(1) As Integer
    Dim vern_endcol(1) As Integer

    fstyr = Range("FirstAll")
    lstyr = Range("LastAll")

    Sheets("Personal P_L").Select
    For i = 1 To 15
    startrow(1) = 1
    startcol(1) = 20 'T
    endrow(1) = 56 + 2 * (lstyr - fstyr)
    endcol(1) = 46 'AT
    With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .Range(.Cells(startrow, startcol), .Cells(endrow, endcol)).Select
    End With
    Selection.PrintOut Copies:=1, Collate:=True
    Next i

    It gives me an error regarding my Range function...any help???

    Thanks!

  2. #2
    Dave Peterson
    Guest

    Re: Range is not being picked up

    Are you writing about this line:

    ..Range(.Cells(startrow, startcol), .Cells(endrow, endcol)).Select

    (assuming that the leading dot got eaten by the posting)

    ..range and both .cells() reference don't belong to the activesheet.pagesetup
    property. They belong to something that can hold a range--like a worksheet.

    And I'm not sure why you tried to use arrays for your variables. And as a rule,
    I never use Integer. Integers can only go to +/- 32k (about). But you can have
    rows up to 65536 (64k).

    And you don't need to select stuff (worksheets or ranges) to work with them.

    I'm not sure if this does what you intended, but it did compile for me:

    Option Explicit
    Sub testme()

    Dim StartRow As Long
    Dim StartCol As Long
    Dim EndRow As Long
    Dim EndCol As Long
    Dim i As Long

    Dim FstYr As Double
    Dim LstYr As Double

    With Worksheets("Personal P_L")
    FstYr = .Range("FirstAll").Value
    LstYr = .Range("LastAll").Value

    'these don't change, so don't put them in the loop.
    With .PageSetup
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    End With

    StartRow = 1
    StartCol = 20 'T
    EndCol = 46 'AT
    EndRow = 56 + 2 * (LstYr - FstYr)

    'why not just print once and make copies:=15????
    For i = 1 To 15
    .Range(.Cells(StartRow, StartCol), _
    .Cells(EndRow, EndCol)).PrintOut Copies:=1, Collate:=True
    Next i

    End With
    End Sub


    Sheeny wrote:
    >
    > Please take a look at my code, I am trying to pick up the range and
    > print it, depending on the analysis years.
    >
    > Dim area As String
    > Dim startrow(1) As Integer
    > Dim startcol(1) As Integer
    > Dim endrow(1) As Integer
    > Dim endcol(1) As Integer
    > Dim vern_startrow(1) As Integer
    > Dim vern_startcol(1) As Integer
    > Dim vern_endrow(1) As Integer
    > Dim vern_endcol(1) As Integer
    >
    > fstyr = Range("FirstAll")
    > lstyr = Range("LastAll")
    >
    > Sheets("Personal P_L").Select
    > For i = 1 To 15
    > startrow(1) = 1
    > startcol(1) = 20 'T
    > endrow(1) = 56 + 2 * (lstyr - fstyr)
    > endcol(1) = 46 'AT
    > With ActiveSheet.PageSetup
    > Orientation = xlLandscape
    > PaperSize = xlPaperLegal
    > Range(.Cells(startrow, startcol), .Cells(endrow,
    > endcol)).Select
    > End With
    > Selection.PrintOut Copies:=1, Collate:=True
    > Next i
    >
    > It gives me an error regarding my Range function...any help???
    >
    > Thanks!
    >
    > --
    > Sheeny
    > ------------------------------------------------------------------------
    > Sheeny's Profile: http://www.excelforum.com/member.php...fo&userid=9082
    > View this thread: http://www.excelforum.com/showthread...hreadid=538967


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1