Using Loops to Run Through Groups of Data

canada01

New Member
Joined
Mar 20, 2017
Messages
10
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 IDVTM IndexParameter NameValue
PD143148
0Position0
PD1431481Speed34
PD1431482Odometer9110
PD1431483Position1
PD1431484Odometer9156
PD1431485Speed56
PD1431486Speed72
PD1431487Position0
PD1431488Odometer9234
PD1431489Position1
PD129834

<tbody>
</tbody>
0Odometer324
PD129834

<tbody>
</tbody>
1Odometer329
PD129834

<tbody>
</tbody>
2Position1
PD129834

<tbody>
</tbody>
3Speed100

<tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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.
 
Upvote 0
Try this:-
NB:- See code notes !!!

Code:
[COLOR="Navy"]Sub[/COLOR] MG21Mar15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
 .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    '[COLOR="Green"][B]This first loop will Create a Unique Range("Areas") relating[/B][/COLOR]
    '[COLOR="Green"][B]to each unique values in column "A"[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
 '[COLOR="Green"][B]This loop, loops through each unique "Session ID"[/B][/COLOR]
     [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(k)
        '[COLOR="Green"][B]This loop, loops through each cell within each Unique "Session ID"[/B][/COLOR]
        '[COLOR="Green"][B]It is here where you decide what you want to do within[/B][/COLOR]
        '[COLOR="Green"][B]each Range relating to each unique "Session ID"[/B][/COLOR]
        
        '[COLOR="Green"][B] This Msgbox shows the value of the Unique ID"[/B][/COLOR]
         '[COLOR="Green"][B]and the value within column "B" for that "ID"[/B][/COLOR]
         MsgBox k & "    " & p.Offset(, 1).Value
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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 !!
 
Upvote 0
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 IDVTM IndexParameter NameValue
PD1431480Position0
PD1431481Speed34
PD1431482Odometer9110
PD1431483Position1
PD1431484Odometer9156
PD1431485Speed56
PD1431486Speed72
PD1431487Position0
PD1431488Odometer9234
PD1431489Position1
PD129834
0Odometer324
PD129834
1Odometer329
PD129834
2Position1
PD129834
3Speed100

<tbody>
</tbody>
 
Upvote 0
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 !!!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top