loop thru series of rows and insert data
Results 1 to 7 of 7

Thread: loop thru series of rows and insert data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default loop thru series of rows and insert data

    I am trying to figure out how to find a missing number in a range of rows. I can get the following to work by hard coding the statements to look at the first range of cells. LowerVal & UpperVal calculated previously (pseudo code below)

    Dim InputRange As Range
    Dim ValueFound As Range
    Dim LowerVal As Integer
    Dim UpperVal As Integer
    Dim S1 As Integer

    'Set search range
    Set InputRange = Range("c2:d6")

    'Search for team that has a bye
    For S1 = LowerVal To UpperVal
    Set ValueFound = InputRange.Find(S1)
    If ValueFound Is Nothing Then
    Sheets("rawdata").Range("C7") = S1, Sheets(“rawdata”).range(“D7”) = “bye”
    End If
    Next S1

    I am trying to figure out a way to loop thru a series of rows. (The rows are in a format like below). The objective is to identify what team has a bye (their team number is missing) by searching the home & away columns for the team numbers, find the missing team number and then write that number (and the word ‘bye’ to a blank row under the searched range. Note: a separate macro has been used to insert a blank row after each date. If it is easier to insert the blank row as part of this query, then the “blank row insert’ query can be tossed out.

    Each season the number of teams, location and the dates will change which will change the amount of data- but not the format. Can I get some help on how to loop thru the raw data, find the missing team for each scheduled date of play and insert their team number in the blank row indicating that they have a bye week?

    A sample of the rawdata worksheet is below

    ColA ColB ColC ColD ColE
    Date Time Home Away Location
    5/7/2019 10:00 AM 8 10 4
    5/7/2019 10:00 AM 1 9 1
    5/7/2019 10:00 AM 7 6 5
    5/7/2019 10:00 AM 3 5 2
    5/7/2019 10:00 AM 11 4 6

    5/14/2019 10:00 AM 2 9 3
    5/14/2019 10:00 AM 6 10 2
    5/14/2019 10:00 AM 7 5 1
    5/14/2019 10:00 AM 1 11 5
    5/14/2019 10:00 AM 8 4 4

    5/21/2019 10:00 AM 3 1 6
    5/21/2019 10:00 AM 5 4 3
    5/21/2019 10:00 AM 6 8 1
    5/21/2019 10:00 AM 10 2 5
    5/21/2019 10:00 AM 9 11 4

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,992
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: loop thru series of rows and insert data

    give this a try.

    Code:
    Sub t()
    Dim fn As Range, gp As Range, lr As Long, ary As Variant
    ary = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        For Each gp In Range("A2:E" & lr).SpecialCells(xlCellTypeConstants).Areas
                For i = LBound(ary) To UBound(ary)
                With gp.Cells(1, 3).Resize(5, 2)
                    Set fn = .Find(ary(i), , xlValues, xlWhole)
                        If fn Is Nothing Then
                            gp.Cells(5, 3).Offset(1) = ary(i)
                            gp.Cells(5, 5).Offset(1) = "Bye"
                            Exit For
                    End If
                End With
            Next
        Next
    End With
    End Sub
    Last edited by JLGWhiz; Jul 31st, 2019 at 05:57 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: loop thru series of rows and insert data

    Thank you for the great code. Is there a way that I can create the array to contain a series of numbers from 1 to the highest team number? I have tried the following to try and dynamically create the contents of the array; but, the gp.cells(5,3).offset(1) = Myarray(i) does not populate the team number. The debug.print Myarray shows numbers 1 thru 11 which is the lowest and highest team number and correct but does not get same results as your great code.

    How can I convert your “ary = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)” to an array of one thru the highest team number because over each season the number of teams will change and not be a constant of 11.

    The code that I have created to try and do this is as follows

    Dim MyArray() As Variant
    'Find Largest Team Number
    Sheets("RawData").Activate
    LargestTeamNum = Application.WorksheetFunction.Max(Range("C:D"))
    'Load the Array
    ReDim MyArray(LargestTeamNum)
    For S1 = 1 To LargestTeamNum
    MyArray(S1) = S1
    Debug.Print MyArray(S1)
    Next

    With ActiveSheet
    lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each gp In Range("A2:E" & lr).SpecialCells(xlCellTypeConstants).Areas
    For i = LBound(MyArray) To UBound(MyArray)
    Debug.Print MyArray(i)
    With gp.Cells(1, 3).Resize(5, 2)
    Set fn = .Find(MyArray(i), , xlValues, xlWhole)
    If fn Is Nothing Then
    gp.Cells(5, 3).Offset(1) = MyArray(i)
    gp.Cells(5, 4).Offset(1) = "Bye"
    Exit For
    End If
    End With
    Next
    Next
    End With

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,992
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: loop thru series of rows and insert data

    Code:
    For i = LBound(MyArray) To UBound(MyArray)
                Debug.Print MyArray(i)
                With gp.Cells(1, 3).Resize(5, 2)
                    Set fn = .Find(MyArray(i), , xlValues, xlWhole)
                    If fn Is Nothing Then
                        gp.Cells(5, 3).Offset(1) = MyArray(i)
                        gp.Cells(5, 4).Offset(1) = "Bye"
                        Exit For
                    End If
                End With
            Next
            Next
    If you change the number of teams participating, it will change the parameters used for the Areas range. ie. 15 Teams would change the number of rows from 5 to 7 for each Area of teams scheduled, so you wouold also have to have a variable there which would be based on the number of teams.

    Code:
    Dim r As Long
    r = Int(LargestTeamNum/2)
    Then you can use

    Code:
    With gp.Cells(1, 3).Resize(r, 2)
    
    gp.Cells(r, 3).Offset(1) = MyArray(i)
    gp.Cells(r, 4).Offset(1) = "Bye"
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: loop thru series of rows and insert data

    I have run the following code and receive the following results.

    In the load the array section, the debug.print myarrary (S1) shows the numbers 1 thru 11. In the Put the team number that has a bye in the blank row, the debug.print myarray(i) does not show any value. The if fn is nothing check falls thru to the 2 gp.cells commands. This looks like my ‘load the array is saying that the numbers 1 thru 11 are loading but the or i = LBound(MyArray) To UBound(MyArray) is not finding them. However, it I put your MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) statement after the load my array using the ‘For S1 = 1 etc’ statement then the code in the put team number… section’ works.

    How can I change the population of the array to be dynamic instead of being loaded by a comma separated set of values?

    BTW, I have tested the code with a different number of teams and it works GREAT !!!!


    Dim r As Long
    Dim fn As Range
    Dim gp As Range
    Dim lr As Long
    Dim MyArray() As Variant
    Dim S1 As Integer
    Dim LargestTeamNum As Integer

    'Activate sheet to work on
    Sheets("RawData").Activate

    'Find Largest Team Number
    LargestTeamNum = Application.WorksheetFunction.Max(Range("C:D"))

    'Load the Array
    ReDim MyArray(LargestTeamNum)
    For S1 = 1 To LargestTeamNum
    MyArray(S1) = S1
    Debug.Print MyArray(S1)
    Next

    'Put the team number that has a bye in the blank row
    ' (old code is commetted out)
    r = Int(LargestTeamNum / 2)
    'if following population of array is used then code below works
    ' MyArray = Array(1, 2, 3, 4, 5, 6, 7)
    With ActiveSheet
    lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each gp In Range("A2:E" & lr).SpecialCells(xlCellTypeConstants).Areas
    For i = LBound(MyArray) To UBound(MyArray)
    Debug.Print MyArray(i)
    'With gp.Cells(1, 3).Resize(5, 2)
    With gp.Cells(1, 3).Resize(r, 2)
    Set fn = .Find(MyArray(i), , xlValues, xlWhole)
    If fn Is Nothing Then
    'gp.Cells(5, 3).Offset(1) = MyArray(i)
    'gp.Cells(5, 4).Offset(1) = "Bye"
    gp.Cells(r, 3).Offset(1) = MyArray(i)
    gp.Cells(r, 4).Offset(1) = "Bye"
    Exit For
    End If
    End With
    Next
    Next
    End With

  6. #6
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: loop thru series of rows and insert data

    Stop the presses. I got it

    Changed MyArray(S1) = S1 to MyArray(S1 - 1) = S1 because array index starts from ZERO.

    My greatest admiration and thanks to JLGWIZ who has the patience of a saint. It is now off to a nap for this old retired man living in Florida trying to learn some excel/vba for our bocceball commissioner.

  7. #7
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,992
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: loop thru series of rows and insert data

    Quote Originally Posted by breilly00 View Post
    Stop the presses. I got it

    Changed MyArray(S1) = S1 to MyArray(S1 - 1) = S1 because array index starts from ZERO.

    My greatest admiration and thanks to JLGWIZ who has the patience of a saint. It is now off to a nap for this old retired man living in Florida trying to learn some excel/vba for our bocceball commissioner.
    Happy to help,
    Regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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