Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Possible Loop? (2002)

    I've finally broken down and need expert assistance. I have a column for each day of the week, and need to extract only the following ranges of each column: cells 5 and 6, then cells 9 and 10, then cells 13 and 14, etc. In other words, give me the data from two specific cells, skip the next 2 cells, then give me the data from the next 2 cells, etc. On top of that, if there is any data at all in the second of these cells, I need to place it in a specific location (cell on another worksheet) based not only on the data in the first cell, but also based on the column it

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

    Re: Possible Loop? (2002)

    You wrote "need to extract only the following ranges ..." Can you explain where you want to extract to? And how to determine what to do with the second of these cells?

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible Loop? (2002)

    Thanks, Hans.
    Your response has made me consider my own question more thoroughly. I only need to extract both values to another worksheet in the same workbook. The first (top) cell contains the ideal expectation, and the cell below it contains the actual results of that expectation, for each day of the week. In between each of these two-cell ranges is garbage I don't want, so I want to skip over those and get the next two-cell range containing the expected and actual. When I am able to put it on the other worksheet, I can perform other analyses on it.

    Thanks,
    Jamesian

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

    Re: Possible Loop? (2002)

    Thanks. More questions: you wrote that you have four worksheets with seven columns each.
    - Are these seven columns contiguous, for example columns B through H, or not?
    - Do you want to extract the data to one worksheet, with 28 columns filled? And if so, is there a specific order?

  5. #5
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible Loop? (2002)

    Yes, for the first question. They are contiguous.

    For the second, if I could get all Mondays in the first 4 columns, all Tuesdays in the 2nd 4, etc.

    Thanks,
    Jamesian

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

    Re: Possible Loop? (2002)

    Here is some code, to go into a general module. You will have to modify the values of the constants to suit your situation, as well as the names of the worksheets in the ExtractCells procedure. To execute, click somewhere in ExtractCells and press F5. Make a backup copy before you try this.

    ' First row to be extracted
    Const StartRow = 5

    ' Number of rows in source
    Const NumRows = 63

    ' First column to be extracted (1 = A, 2 = B, etc.)
    Const StartCol = 2

    ' Number of columns to be extracted
    Const NumCols = 7

    ' Name of destination worksheet
    Const strTarget = "Sheet5"

    Sub ExtractCells()
    ExtractSheet "Sheet1", 1
    ExtractSheet "Sheet2", 2
    ExtractSheet "Sheet3", 3
    ExtractSheet "Sheet4", 4
    End Sub

    Sub ExtractSheet(strSource As String, lngOffset As Long)
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngRow As Long
    Dim lngCol As Long

    Set wshSource = Worksheets(strSource)
    Set wshTarget = Worksheets(strTarget)

    For lngCol = 0 To NumCols - 1
    For lngRow = 0 To NumRows 2 Step 2
    wshTarget.Cells(lngRow + 1, 4 * lngCol + lngOffset) = _
    wshSource.Cells(lngRow * 2 + StartRow, lngCol + StartCol)
    wshTarget.Cells(lngRow + 2, 4 * lngCol + lngOffset) = _
    wshSource.Cells(lngRow * 2 + StartRow + 1, lngCol + StartCol)
    Next lngRow
    Next lngCol

    Set wshTarget = Nothing
    Set wshSource = Nothing
    End Sub

  7. #7
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible Loop? (2002)

    Hans, it's beautiful and works perfectly. Thank you for your invaluable time.

    Thanks,
    Jamesian

Posting Permissions

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