Using Loops to Run Through Groups of Data

[canada01]

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 <tbody> </tbody>	0	Odometer	324
PD129834 <tbody> </tbody>	1	Odometer	329
PD129834 <tbody> </tbody>	2	Position	1
PD129834 <tbody> </tbody>	3	Speed	100

<tbody>
</tbody>

 

[canada01]

Session ID is Column "A", there are time stamps and other data that I don't want to get rid of in the columns B-H

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 !!!

That's correct. The code currently works like how I showed in the example. It looks at the WHOLE sheet. I would like for it to do what you describe above--look at "Position" ONLY in the current Session ID. My trouble is that when I copy the code below (which works on the whole sheet) into the loop your created, Excel crashes with a run-time error. I do not know which run-time error, because the program closes before I can read the numbers.

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 Not PrevValuePSO Is Nothing And 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

 

[MickG]

Try this:-
I have written the code so "Session ID" is in column "A" and "Parameter name " is in column "J", I have also included the criteria as "Passenger Seat Occupancy"

[COLOR="Navy"]Sub[/COLOR] MG23Mar57
[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"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/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="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"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
     nVal = ""
     [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(k)
         [COLOR="Navy"]If[/COLOR] p.Offset(, 9).Value = "Passenger Seat Occupancy" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nVal = "" [COLOR="Navy"]Then[/COLOR]
                nVal = p.Offset(, 10).Value
            [COLOR="Navy"]Else[/COLOR]
               [COLOR="Navy"]If[/COLOR] Not Val(nVal) = p.Offset(, 10).Value [COLOR="Navy"]Then[/COLOR]
                  p.EntireRow.Interior.Color = vbYellow
                  nVal = p.Offset(, 10).Value
               [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[canada01]

Thanks, Mick! I see how you used the offset command rather than column names to identify the cells with the parameter values.

Could you please explain what the purpose of introducing nVal is? Is this just a name you used in place of "ValuePSO"? I ask because I'll have to recreate this several times to identify other pieces of data, and I would like to be able to give them unique names so that future users will know what is being referenced.

 

[MickG]

When the code find the value "Passenger Seat Occupancy", then nval is given the value in the next column.
Initially it has no value, but on finding the first "Passenger Seat Occupancy", it is given the value in the next cell.
When the code loops again to find the next "Passenger Seat Occupancy" the value in the next column is checked against it.
If different the line is highlighted and the variable nval is given the new value, so that it can be used for checking the next "Passenger Seat Occupancy" offset value .
When a new "Session ID" (next K value) appears nVal is set to empty and the process start again.

 

[canada01]

Hi MickG,

Thanks! I ended up stepping through the code in Debug and watched what it was doing.

Thanks again for all your help.