Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Using Loops to Run Through Groups of Data

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Loops to Run Through Groups of Data

    Hello,

    I am new to VBA and am learning as I go for a work project.
    I need to cycle through groups of data (anywhere from 1-319 lines long in a file with 155,000+ lines of data) and perform some operations on them.

    I am looking for recommendations on how to create a loop (looking at For/Each/Next loops right now) that will look at the information in column A (data-acq. session ID) to define each session as a "group" of data, so that the actions thereafter are only performed on that group, and not on the entire sheet. An example of the data is screenshot-ed below.

    Thanks in advance for any advice!

    Session ID VTM Index Parameter Name Value
    PD143148 0 Position 0
    PD143148 1 Speed 34
    PD143148 2 Odometer 9110
    PD143148 3 Position 1
    PD143148 4 Odometer 9156
    PD143148 5 Speed 56
    PD143148 6 Speed 72
    PD143148 7 Position 0
    PD143148 8 Odometer 9234
    PD143148 9 Position 1
    PD129834
    0 Odometer 324
    PD129834
    1 Odometer 329
    PD129834
    2 Position 1
    PD129834
    3 Speed 100

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Hi canada01,

    Welcome to the MrExcel Board.

    I am not sure I am following your requirement, so to rephrase using your sample above.

    You would like to see the above data as two separate groups that you could run a loop on that would either loop through group PD143148 or group PD129834.

    igold
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Hi igold,

    Yes, that's mostly correct. I would like the loop to run through group PD143148 and then when it is complete, run in group PD129834, etc.

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Try this:-
    NB:- See code notes !!!

    Code:
    Sub MG21Mar15
    Dim Rng As Range, Dn As Range, n As Long
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
     .CompareMode = vbTextCompare
    For Each Dn In Rng
        'This first loop will Create a Unique Range("Areas") relating
        'to each unique values in column "A"
        If Not .Exists(Dn.Value) Then
            .Add Dn.Value, Dn
        Else
            Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
        End If
    Next
    Dim k As Variant, p As Variant
     For Each k In .keys
     'This loop, loops through each unique "Session ID"
         For Each p In .Item(k)
            'This loop, loops through each cell within each Unique "Session ID"
            'It is here where you decide what you want to do within
            'each Range relating to each unique "Session ID"
            
            ' This Msgbox shows the value of the Unique ID"
             'and the value within column "B" for that "ID"
             MsgBox k & "    " & p.Offset(, 1).Value
        Next p
    Next k
    End With
    End Sub
    Regards Mick

  5. #5
    New Member
    Join Date
    Mar 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Hi Mick,

    Thanks a bunch, this makes a lot of sense--I just didn't know how to do it! I'll let you know if I have further questions!

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    You're welcome

  7. #7
    New Member
    Join Date
    Mar 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Hi Mick or anyone else who can help out,

    I'm having trouble modifying a code that worked on the whole sheet to work within the loop you created above. I've added the below section after the line "For Each p In .Item(k)" and if I run it, Excel crashes out and displays an runtime error message for a split second, but I never have enough time to take a screen grab or catch which error it is. This section of code works if it's run as its own sub, with nothing else. When I add it into the loop, it crashes out. I have also swapped "Worksheets(1)" in the first line with ".Item(k)" and the same result happens.

    With Worksheets(1).Range("J1", ActiveCell.SpecialCells(xlLastCell))
    Set PSO = .Find("Passenger Seat Occupancy", LookIn:=xlValues)
    If Not PSO Is Nothing Then
    firstAddress = PSO.Address
    Do
    Set ValuePSO = PSO.Offset(, 1)
    Set PreviousPSO = .FindPrevious(PSO)
    Set PrevValuePSO = PreviousPSO.Offset(, 1)
    If ValuePSO <> PrevValuePSO Then
    ValuePSO.EntireRow.Interior.ColorIndex = 33
    End If
    Set PSO = .FindNext(PSO)
    Loop While Not PSO Is Nothing And PSO.Address <> firstAddress
    End If
    End With


    Thanks in advanced for the assistance!

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    If you can show me what you are trying to do in context of your original data (Show new example if required with expected result)
    I will take another look !!

  9. #9
    New Member
    Join Date
    Mar 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    Yeah, sure! So I'm identifying the value of the Passenger Seat Occupancy--We'll use Position in my example data.

    The code looks at all the values of "Position" by finding when "Position" shows up in the Parameter Column and then finds the value in the cell immediately to the right. If the value of the current "Position" is different than the previous value, then the row is highlighted. I'll use bold in the example below. The last value of "Position" isn't highlighted since the value is the same as the one before. I just want to see when it changes.

    Now, in my actual data (which I unfortunately cannot share), the parameter name is in Column J and I'm looking for "Passenger Seat Occupancy" instead of "Position".


    Session ID VTM Index Parameter Name Value
    PD143148 0 Position 0
    PD143148 1 Speed 34
    PD143148 2 Odometer 9110
    PD143148 3 Position 1
    PD143148 4 Odometer 9156
    PD143148 5 Speed 56
    PD143148 6 Speed 72
    PD143148 7 Position 0
    PD143148 8 Odometer 9234
    PD143148 9 Position 1
    PD129834 0 Odometer 324
    PD129834 1 Odometer 329
    PD129834 2 Position 1
    PD129834 3 Speed 100

  10. #10
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Loops to Run Through Groups of Data

    If "parameter Name" column is "J" does that make "Session ID" "H" or some other column.

    In relation to my first code , Can I assume that the highlighting criteria is related to Individual set of unique "Session ID's", in which case The last "Session ID" (PD 129834) would not be highlighted anyway because its a different "ID" and has only one cell with the word "Position". Please confirm !!!

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
  •  


DMCA.com