Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Looping (Excel 2000)

    I am trying to have VBA loop in an Excel file. I have a range set up and am trying to have VBA select each value in the range and paste to another cell then print. I want it to repeat this process throuout the entire range.

    Any help would be apprecaited.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Looping (Excel 2000)

    Welcome to Woody's Lounge!

    In many situations, it is not really necessary to select cells in VBA code. You could use something like the following:

    Dim oCell As Range
    For Each oCell In Range("F1:F10").Cells
    oCell.Copy Destination:=Range("A1")
    ActiveSheet.PrintOut
    Next oCell

    This will loop through the cells in F1:F10, and copy each in turn to cell A1, then print the active sheet. You can modify this as needed.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Looping (Excel 2000)

    The code Hans gave you will use a loop to copy a range of cells to cell A1, which means that you will end up with the last cell in A1 copied to cell A1. Probably not very usefull. The code below uses a loop to copy the range F1:F10 to the range A1:A10, and then print the worksheet.

    <code>
    Dim I As Long
    For I = 0 To 9
    Range("F1").Offset(I, 0).Copy Destination:=Range("A1").Offset(I, 0)
    Next I
    ActiveSheet.PrintOut
    </code>

    However, you don't need a loop to do this. The following does the same thing and is more efficient:

    <code>
    Range("F1:F10").Copy Destination:=Range("A1")
    ActiveSheet.PrintOut
    </code>
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Looping (Excel 2000)

    I interpreted the question differently - the PrintOut command is within the loop, so you get a print for each value. The original poster can decide which reply meets his needs.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Looping (Excel 2000)

    Seeing his other post, I think that your interpretagions was probably the correct one.
    Legare Coleman

Posting Permissions

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