Results 1 to 2 of 2

Thread: Array's in Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2000
    Posts
    15

    Question

    Does anyone know how to apply an array to the following code so that I can use this for 4 sheets instead of replicating the code 4 times?

    Thanks in advance for your help!!!!!!!!!


    Sub DoLoop()
    '
    ' DoLoop Macro

    '
    Dim i As Integer
    Dim sNCell As String
    Dim sOCell As String



    'Worksheets("Sheet1").Range("N2:R2").Copy 'Range to be copied

    Set currentCell = Worksheets("Sheet1").Range("B2")

    i = 1
    Do While Not IsEmpty(currentCell)

    '''' The following cells are being 'defined' and incremented by 1
    '''' each time thru the loop to continue going down the spreadsheet

    i = i + 1
    sNCell = "N" & i '' N_ cell
    sOCell = "O" & i '' O_ cell
    sPCell = "P" & i '' P_ cell
    sQCell = "Q" & i '' Q_ cell
    sRCell = "R" & i '' R_ cell

    '''' the next line is only for debugging purposes; it can be taken out
    '''' go to the 'View' menu, and then 'Immediate Window' to see its
    '''' results; it prints whatever you want in this window; very handy
    Debug.Print currentCell

    '''' each of the following lines copies the formula from the '?2' row
    '''' and pastes into the 'defined' row from above
    Worksheets("Sheet1").Range("N2").Copy _
    Destination:=Worksheets("Sheet1").Range(sNCell)

    Worksheets("Sheet1").Range("O2").Copy _
    Destination:=Worksheets("Sheet1").Range(sOCell)

    ''' Next increment by 1 down to the next cell in the B column
    ''' to see if the loop should continue looping
    Set nextCell = currentCell.Offset(1, 0)

    Set currentCell = nextCell

    Debug.Print currentCell

    Loop

    Application.CutCopyMode = False
    Range("A2").Select



    End Sub






    Confused

  2. #2

    Thread Starter
    New Member
    Join Date
    Oct 2000
    Posts
    15
    Update on my question........
    I have modified my code as follows:

    Dim myWS as WorkSheet

    For Each myWS in WorkSheets

    ' ....
    'Code
    ' ....

    Next

    I replaced each Sheet1 with myWS

    This works with one exception.... The code copies to every third line instead of each line to the end of file for each tab.

    Any ideas??? Help is appreciated.
    Confused

Posting Permissions

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



Click Here to Expand Forum to Full Width