VBA to make a list depending on the event
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA to make a list depending on the event

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to make a list depending on the event

    Hello All,

    I have recorded (that's all I can do ) a macro that filters a set of data. At the moment it filters a set of vehicles and a set of locations. But my problem is that on daily basis, different vehicles may go to the location that is required. Thus I end up manually changing the selection of vehicles that have gone to a particular location (AVA in my case). Can I please get someone to look at the code below and tell me how I can get it to first make a list of vehicles that have "AVA" as one of the locations in the data and then create filters based on this list of vehicles.
    The code is:
    Code:
    Sub AVA()With ActiveSheet
    Dim rwnm As Long
    rwnm = Range("A" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        ActiveSheet.ShowAllData
        ActiveSheet.Range("A1:H" & rwnm).AutoFilter Field:=8, Criteria1:=Array( _
            "AVA", "SXS", "Depot"), Operator:= _
            xlFilterValues
        ActiveSheet.Range("A1:H" & rwnm).AutoFilter Field:=2, Criteria1:=Array( _
            "150", "151", "152", "153", "50", "52", "53", "54", "8", "9"), _
            Operator:=xlFilterValues
        Range("A1").Select
    End With
    End Sub
    Thanks and Regards
    Asad
    Last edited by asad; Jul 2nd, 2019 at 06:41 PM.

  2. #2
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    Any help please????

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    Anyone??

  4. #4
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    Okay,

    At last after few days of searching, I found something that I thought can be used. Here is the modified code that I have changed
    Code:
    Sub Avalon()
    Dim oRange As Range
    
    
    Dim dict As Dictionary
    Dim vArray As Variant
    Dim vItem As Variant
    Dim sKey As String
    Dim sValue As String
    Dim iCompare_TRUE As Integer
    
    
    Dim lCnt As Long
    Dim lCnt_Rows As Long
    
    
    With ActiveSheet
    Dim rwnm As Long
        
    On Error Resume Next
    ActiveSheet.ShowAllData
    
    
    rwnm = Range("A" & Rows.Count).End(xlUp).Row
    
    
    Set dict = New Dictionary
    
    
    Set oRange = ThisWorkbook.Sheets(1).Range("A1:H" & rwnm)
    
    
    For lCnt = 1 To oRange.Rows.Count
        sKey = oRange(lCnt, 2)
        sValue = oRange(lCnt, 8)
        iCompare_TRUE = StrComp(sValue, "AVA")
        If Not dict.exists(sKey) And iCompare_TRUE = 0 Then
            With dict
                .Add sKey, sValue
            End With
        End If
    Next lCnt
    
    
    ReDim vArray(1 To dict.Count)
    vArray = dict.Keys
        ActiveSheet.Range("A1:H" & rwnm).AutoFilter Field:=8, Criteria1:=Array( _
            "AVA", "SXS", "TDepot"), Operator:= _
            xlFilterValues
        ActiveSheet.Range("A1:H" & rwnm).AutoFilter Field:=2, Criteria1:=Array( _
            vArray), _
            Operator:=xlFilterValues
        Range("A1").Select
    End With
    End Sub
    Its main part has been taken from https://stackoverflow.com/questions/...another-column

    But I am unable to get it to work. The line "Dim dict As Dictionary" gets highlighted and gives me an error message - "User-defined type not defined"

    Any ideas how I can fix this?
    Last edited by asad; Jul 4th, 2019 at 06:44 PM.

  5. #5
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    If somebody wants to look at the data, I will try to post it here as much as I can. Daily data goes beyond 35000 rows. With lot more of locations and vehicles in the mix, it becomes really hard for other people when they have to prepare reports in my absence.
    Code:
    Registration Plate Display Name Date Time Event Type Odometer Driver Location
    101 4/07/2019 4:50:00 Exit 253671 TDepot
    101 4/07/2019 5:24:00 Enter 253694.4 SXS
    101 4/07/2019 5:38:00 Exit 253695.4 SXS
    101 4/07/2019 6:43:00 Enter 253741.7 SXS
    101 4/07/2019 6:54:00 Exit 253742.7 SXS
    101 4/07/2019 7:26:00 Enter 253766.7 TDepot
    101 4/07/2019 7:37:00 Exit 253767 TDepot
    101 4/07/2019 8:15:00 Enter 253790.4 SXS
    101 4/07/2019 8:28:00 Exit 253791.6 SXS
    101 4/07/2019 9:33:00 Enter 253837.8 SXS
    101 4/07/2019 9:46:00 Exit 253838.8 SXS
    101 4/07/2019 10:51:00 Enter 253885 SXS
    101 4/07/2019 11:03:00 Exit 253886.1 SXS
    101 4/07/2019 11:33:00 Enter 253910.1 TDepot
    101 4/07/2019 11:40:00 Exit 253910.4 TDepot
    101 4/07/2019 12:22:00 Enter 253933.8 SXS
    101 4/07/2019 12:33:00 Exit 253934.8 SXS
    101 4/07/2019 13:38:00 Enter 253981.8 SXS
    101 4/07/2019 13:52:00 Exit 253982.9 SXS
    101 4/07/2019 14:59:00 Enter 254029 SXS
    101 4/07/2019 15:09:00 Exit 254030 SXS
    101 4/07/2019 15:43:00 Enter 254054.1 TDepot
    101 4/07/2019 16:03:00 Exit 254054.4 TDepot
    101 4/07/2019 16:41:00 Enter 254077.9 SXS
    101 4/07/2019 16:54:00 Exit 254078.9 SXS
    101 4/07/2019 18:07:00 Enter 254125 SXS
    101 4/07/2019 18:18:00 Exit 254126 SXS
    101 4/07/2019 19:25:00 Enter 254172.3 SXS
    101 4/07/2019 19:37:00 Exit 254173.3 SXS
    101 4/07/2019 20:12:00 Enter 254197.4 TDepot
    102 4/07/2019 0:09:00 Exit 278681.7 SXS
    102 4/07/2019 0:33:00 Enter 278705.7 TDepot
    102 4/07/2019 11:16:00 Exit 278706.2 TDepot
    102 4/07/2019 11:49:00 Enter 278729.7 SXS
    102 4/07/2019 12:02:00 Exit 278730.8 SXS
    102 4/07/2019 13:08:00 Enter 278777.7 SXS
    102 4/07/2019 13:22:00 Exit 278778.7 SXS
    102 4/07/2019 14:27:00 Enter 278825.8 SXS
    102 4/07/2019 14:39:00 Exit 278826.7 SXS
    102 4/07/2019 15:10:00 Enter 278850.8 TDepot
    102 4/07/2019 15:36:00 Exit 278851.1 TDepot
    102 4/07/2019 16:15:00 Enter 278874.6 SXS
    102 4/07/2019 16:28:00 Exit 278875.7 SXS
    102 4/07/2019 17:38:00 Enter 278922.6 SXS
    102 4/07/2019 17:59:00 Exit 278923.7 SXS
    102 4/07/2019 18:58:00 Enter 278969 SXS
    102 4/07/2019 19:15:00 Exit 278970 SXS
    102 4/07/2019 19:46:00 Enter 278994.1 TDepot
    103 4/07/2019 6:28:00 Exit 285021.5 TDepot
    103 4/07/2019 7:04:00 Enter 285045.1 SXS
    103 4/07/2019 7:14:00 Exit 285046.1 SXS
    103 4/07/2019 8:24:00 Enter 285093.1 SXS
    103 4/07/2019 8:35:00 Exit 285094.1 SXS
    103 4/07/2019 9:37:00 Enter 285141.1 SXS
    103 4/07/2019 9:53:00 Exit 285142.1 SXS
    103 4/07/2019 10:22:00 Enter 285166.2 TDepot
    103 4/07/2019 12:08:00 Exit 285166.4 TDepot
    103 4/07/2019 12:54:00 Enter 285189.9 SXS
    103 4/07/2019 13:03:00 Exit 285191 SXS
    103 4/07/2019 14:07:00 Enter 285238.1 SXS
    103 4/07/2019 14:22:00 Exit 285239.1 SXS
    103 4/07/2019 14:52:00 Enter 285263.1 TDepot
    103 4/07/2019 15:34:00 Exit 285263.4 TDepot
    103 4/07/2019 16:20:00 Enter 285286.9 SXS
    103 4/07/2019 16:34:00 Exit 285287.9 SXS
    103 4/07/2019 17:45:00 Enter 285335 SXS
    103 4/07/2019 18:02:00 Exit 285335.9 SXS
    103 4/07/2019 19:10:00 Enter 285382.1 SXS
    103 4/07/2019 19:19:00 Exit 285383.2 SXS
    103 4/07/2019 19:49:00 Enter 285407.3 TDepot
    103 4/07/2019 20:39:00 Exit 285407.5 TDepot
    103 4/07/2019 21:14:00 Enter 285431 SXS
    103 4/07/2019 21:29:00 Exit 285432 SXS
    103 4/07/2019 22:24:00 Enter 285478.2 SXS
    103 4/07/2019 22:39:00 Exit 285479.2 SXS
    103 4/07/2019 23:35:00 Enter 285525.4 SXS
    103 4/07/2019 23:49:00 Exit 285526.4 SXS
    104 4/07/2019 0:13:00 Enter 318890.4 SXS
    104 4/07/2019 0:28:00 Exit 318891.5 SXS
    104 4/07/2019 1:22:00 Enter 318937.6 SXS
    104 4/07/2019 1:51:00 Exit 318938.6 SXS
    104 4/07/2019 2:55:00 Enter 318984.8 SXS
    104 4/07/2019 3:05:00 Exit 318985.3 SXS
    104 4/07/2019 3:11:00 Enter 318985.3 SXS
    104 4/07/2019 3:22:00 Exit 318985.9 SXS
    104 4/07/2019 3:46:00 Enter 319010 TDepot
    104 4/07/2019 4:02:00 Exit 319010.1 TDepot
    104 4/07/2019 4:02:00 Enter 319010.2 TDepot
    104 4/07/2019 4:14:00 Exit 319010.4 TDepot
    104 4/07/2019 4:54:00 Enter 319033.9 SXS
    104 4/07/2019 5:08:00 Exit 319035 SXS
    104 4/07/2019 6:03:00 Enter 319081.2 SXS
    104 4/07/2019 6:15:00 Exit 319082.2 SXS
    104 4/07/2019 7:23:00 Enter 319129.2 SXS
    104 4/07/2019 7:34:00 Exit 319130.2 SXS
    104 4/07/2019 8:06:00 Enter 319154.4 TDepot
    104 4/07/2019 8:30:00 Exit 319154.6 TDepot
    104 4/07/2019 9:08:00 Enter 319178.2 SXS
    104 4/07/2019 9:24:00 Exit 319179 SXS
    104 4/07/2019 10:34:00 Enter 319226 SXS
    104 4/07/2019 10:45:00 Exit 319227 SXS
    104 4/07/2019 11:15:00 Enter 319251.1 TDepot
    104 4/07/2019 11:29:00 Exit 319251.5 TDepot
    104 4/07/2019 12:06:00 Enter 319274.9 SXS
    104 4/07/2019 12:20:00 Exit 319276 SXS
    104 4/07/2019 13:28:00 Enter 319322.9 SXS
    104 4/07/2019 13:40:00 Exit 319324 SXS
    104 4/07/2019 14:07:00 Enter 319348.1 TDepot
    104 4/07/2019 14:32:00 Exit 319348.3 TDepot
    104 4/07/2019 15:10:00 Enter 319371.8 SXS
    104 4/07/2019 15:23:00 Exit 319372.8 SXS
    104 4/07/2019 16:35:00 Enter 319419.9 SXS
    104 4/07/2019 16:46:00 Exit 319420.8 SXS
    104 4/07/2019 17:58:00 Enter 319467.9 SXS
    104 4/07/2019 18:09:00 Exit 319468.8 SXS
    104 4/07/2019 18:46:00 Enter 319493 TDepot
    104 4/07/2019 19:18:00 Exit 319493.2 TDepot
    104 4/07/2019 20:20:00 Enter 319517.3 SXS
    104 4/07/2019 20:28:00 Exit 319518.4 SXS
    104 4/07/2019 21:24:00 Enter 319564.6 SXS
    104 4/07/2019 21:38:00 Exit 319565.6 SXS
    104 4/07/2019 22:05:00 Enter 319589.7 TDepot
    104 4/07/2019 23:15:00 Exit 319589.9 TDepot
    104 4/07/2019 23:15:00 Enter 319589.9 TDepot
    104 4/07/2019 23:20:00 Exit 319590.1 TDepot
    150 4/07/2019 6:24:00 Exit 190111.4 TDepot
    150 4/07/2019 6:45:00 Enter 190132.8 SXS
    150 4/07/2019 7:03:00 Exit 190133.9 SXS
    150 4/07/2019 7:31:00 Enter 190164.5 WRSL
    150 4/07/2019 7:33:00 Exit 190164.7 WRSL
    150 4/07/2019 7:55:00 Enter 190189.5 AVA
    150 4/07/2019 8:08:00 Exit 190189.8 AVA
    150 4/07/2019 9:29:00 Enter 190192.5 AVA
    150 4/07/2019 9:58:00 Exit 190192.8 A.De Silva AVA
    150 4/07/2019 10:53:00 Enter 190248 A.De Silva SXS
    150 4/07/2019 11:05:00 Exit 190249 A.De Silva SXS
    150 4/07/2019 11:26:00 Enter 190271.1 A.De Silva TDepot
    150 4/07/2019 12:50:00 Exit 190271.2 TDepot
    150 4/07/2019 13:05:00 Enter 190283.9 TDepot
    150 4/07/2019 17:14:00 Exit 190284 A.Hill TDepot
    150 4/07/2019 17:37:00 Enter 190305.5 A.Hill SXS
    150 4/07/2019 18:04:00 Exit 190306.4 A.Hill SXS
    150 4/07/2019 18:47:00 Enter 190336.4 A.Hill WRSL
    150 4/07/2019 18:48:00 Exit 190336.7 A.Hill WRSL
    150 4/07/2019 19:09:00 Enter 190361.4 A.Hill AVA
    150 4/07/2019 19:58:00 Exit 190361.7 A.Hill AVA
    151 4/07/2019 3:06:00 Exit 174667.5 D.NG TDepot
    151 4/07/2019 3:22:00 Enter 174688.9 D.NG SXS
    151 4/07/2019 4:03:00 Exit 174690.1 D.NG SXS
    151 4/07/2019 4:30:00 Enter 174720.7 D.NG WRSL
    151 4/07/2019 4:30:00 Exit 174720.9 D.NG WRSL
    151 4/07/2019 4:51:00 Enter 174745.6 D.NG AVA
    151 4/07/2019 5:02:00 Exit 174746 D.NG AVA
    151 4/07/2019 5:46:00 Enter 174801.1 D.NG SXS
    151 4/07/2019 6:05:00 Exit 174801.9 D.NG SXS
    151 4/07/2019 6:31:00 Enter 174832.5 D.NG WRSL
    151 4/07/2019 6:31:00 Exit 174832.6 D.NG WRSL
    151 4/07/2019 6:54:00 Enter 174857.4 D.NG AVA
    151 4/07/2019 7:00:00 Exit 174857.8 D.NG AVA
    151 4/07/2019 8:01:00 Enter 174860.4 AVA
    151 4/07/2019 10:18:00 Exit 174860.8 D.NG AVA
    151 4/07/2019 11:07:00 Enter 174915.9 D.NG SXS
    151 4/07/2019 11:15:00 Exit 174917 D.NG SXS
    151 4/07/2019 11:36:00 Enter 174939.1 D.NG TDepot
    151 4/07/2019 14:48:00 Exit 174939.4 K.McLachlan TDepot
    151 4/07/2019 15:07:00 Enter 174960.7 K.McLachlan SXS
    151 4/07/2019 15:23:00 Exit 174961.7 K.McLachlan SXS
    151 4/07/2019 16:02:00 Enter 174992.3 K.McLachlan WRSL
    151 4/07/2019 16:03:00 Exit 174992.6 K.McLachlan WRSL
    151 4/07/2019 16:26:00 Enter 175017.3 K.McLachlan AVA
    151 4/07/2019 16:38:00 Exit 175017.7 K.McLachlan AVA
    151 4/07/2019 17:31:00 Enter 175020.4 K.McLachlan AVA
    151 4/07/2019 17:41:00 Exit 175020.7 K.McLachlan AVA
    151 4/07/2019 18:04:00 Enter 175044.6 K.McLachlan WRSL
    151 4/07/2019 18:05:00 Exit 175044.8 K.McLachlan WRSL
    151 4/07/2019 18:37:00 Enter 175075.7 K.McLachlan SXS
    151 4/07/2019 18:49:00 Exit 175076.8 K.McLachlan SXS
    151 4/07/2019 21:24:00 Enter 175131.7 K.McLachlan AVA
    151 4/07/2019 21:42:00 Exit 175132.1 K.McLachlan AVA
    151 4/07/2019 22:03:00 Enter 175156 K.McLachlan WRSL
    151 4/07/2019 22:04:00 Exit 175156.1 K.McLachlan WRSL
    151 4/07/2019 22:31:00 Enter 175187 K.McLachlan SXS
    151 4/07/2019 22:41:00 Exit 175188.1 K.McLachlan SXS
    151 4/07/2019 23:01:00 Enter 175210.2 K.McLachlan TDepot
    152 4/07/2019 7:09:00 Enter 83123.38 N.Finlayson SXS
    152 4/07/2019 8:02:00 Exit 83124.37 N.Finlayson SXS
    152 4/07/2019 8:31:00 Enter 83155.08 N.Finlayson WRSL
    152 4/07/2019 8:32:00 Exit 83155.25 N.Finlayson WRSL
    152 4/07/2019 8:54:00 Enter 83180 N.Finlayson AVA
    152 4/07/2019 8:57:00 Exit 83180.31 N.Finlayson AVA
    152 4/07/2019 9:16:00 Enter 83183 N.Finlayson AVA
    152 4/07/2019 9:30:00 Exit 83183.33 N.Finlayson AVA
    152 4/07/2019 9:52:00 Enter 83207.24 N.Finlayson WRSL
    152 4/07/2019 9:53:00 Exit 83207.41 N.Finlayson WRSL
    152 4/07/2019 10:27:00 Enter 83238.26 N.Finlayson SXS
    152 4/07/2019 11:03:00 Exit 83239.4 N.Finlayson SXS
    152 4/07/2019 11:31:00 Enter 83270.04 N.Finlayson WRSL
    152 4/07/2019 11:32:00 Exit 83270.25 N.Finlayson WRSL
    152 4/07/2019 11:54:00 Enter 83294.98 N.Finlayson AVA
    152 4/07/2019 11:57:00 Exit 83295.3 N.Finlayson AVA
    152 4/07/2019 13:07:00 Enter 83352.66 Z.Poole SXS
    152 4/07/2019 13:23:00 Exit 83353.74 Z.Poole SXS
    152 4/07/2019 13:54:00 Enter 83384.43 Z.Poole WRSL
    152 4/07/2019 13:56:00 Exit 83384.69 Z.Poole WRSL
    152 4/07/2019 14:18:00 Enter 83409.39 Z.Poole AVA
    152 4/07/2019 14:27:00 Exit 83409.71 Z.Poole AVA
    152 4/07/2019 15:23:00 Enter 83412.4 Z.Poole AVA
    152 4/07/2019 16:06:00 Exit 83412.76 Z.Poole AVA
    152 4/07/2019 16:55:00 Enter 83467.89 Z.Poole SXS
    152 4/07/2019 17:53:00 Exit 83468.87 Z.Poole SXS
    152 4/07/2019 18:41:00 Enter 83498.87 Z.Poole WRSL
    152 4/07/2019 18:43:00 Exit 83499.12 Z.Poole WRSL
    152 4/07/2019 19:05:00 Enter 83523.82 Z.Poole AVA
    152 4/07/2019 19:13:00 Exit 83524.15 Z.Poole AVA
    152 4/07/2019 20:13:00 Enter 83526.84 A.Hill AVA
    152 4/07/2019 20:50:00 Exit 83527.19 A.Hill AVA
    152 4/07/2019 21:10:00 Enter 83551.1 A.Hill WRSL
    152 4/07/2019 21:11:00 Exit 83551.29 A.Hill WRSL
    152 4/07/2019 21:38:00 Enter 83582.17 A.Hill SXS
    152 4/07/2019 21:56:00 Exit 83583.3 A.Hill SXS
    152 4/07/2019 22:15:00 Enter 83605.31 A.Hill TDepot
    153 4/07/2019 0:26:00 Enter 95184.42 S.Amireddy SXS
    153 4/07/2019 0:48:00 Exit 95185.37 S.Amireddy SXS
    153 4/07/2019 5:24:00 Enter 95295.13 K.Woong Lee SXS
    153 4/07/2019 6:22:00 Exit 95296.1 K.Woong Lee SXS
    153 4/07/2019 6:50:00 Enter 95326.82 K.Woong Lee WRSL
    153 4/07/2019 6:50:00 Exit 95326.99 K.Woong Lee WRSL
    153 4/07/2019 7:14:00 Enter 95351.74 K.Woong Lee AVA
    153 4/07/2019 7:18:00 Exit 95352.13 K.Woong Lee AVA
    153 4/07/2019 8:19:00 Enter 95354.74 K.Woong Lee AVA
    153 4/07/2019 9:13:00 Exit 95355.09 K.Woong Lee AVA
    153 4/07/2019 10:01:00 Enter 95410.25 K.Woong Lee SXS
    153 4/07/2019 10:12:00 Exit 95411.29 K.Woong Lee SXS
    153 4/07/2019 12:52:00 Enter 95466.17 S.Gironcel AVA
    153 4/07/2019 14:10:00 Exit 95466.53 S.Gironcel AVA
    153 4/07/2019 14:36:00 Enter 95490.37 S.Gironcel WRSL
    153 4/07/2019 14:37:00 Exit 95490.6 S.Gironcel WRSL
    153 4/07/2019 15:09:00 Enter 95521.51 S.Gironcel SXS
    153 4/07/2019 15:21:00 Exit 95522.27 S.Gironcel SXS
    153 4/07/2019 17:45:00 Enter 95631.71 S.Amireddy SXS
    153 4/07/2019 18:24:00 Exit 95632.74 S.Amireddy SXS
    153 4/07/2019 19:02:00 Enter 95663.38 S.Amireddy WRSL
    153 4/07/2019 19:02:00 Exit 95663.55 S.Amireddy WRSL
    153 4/07/2019 19:24:00 Enter 95688.32 S.Amireddy AVA
    153 4/07/2019 19:34:00 Exit 95688.63 S.Amireddy AVA
    153 4/07/2019 20:49:00 Enter 95691.34 S.Amireddy AVA
    153 4/07/2019 21:11:00 Exit 95691.67 S.Amireddy AVA
    153 4/07/2019 21:55:00 Enter 95746.56 S.Amireddy SXS
    153 4/07/2019 22:08:00 Exit 95747.52 S.Amireddy SXS
    153 4/07/2019 23:12:00 Enter 95802.4 S.Amireddy AVA
    153 4/07/2019 23:47:00 Exit 95802.77 S.Amireddy AVA
    202 4/07/2019 8:57:00 Enter 119524.4 TDepot
    202 4/07/2019 11:17:00 Exit 119524.7 TDepot
    203 4/07/2019 5:13:00 Enter 126153.6 B.Bennett WRSL
    203 4/07/2019 5:14:00 Exit 126153.8 B.Bennett WRSL
    203 4/07/2019 5:27:00 Enter 126155.3 B.Bennett WRSL
    203 4/07/2019 5:35:00 Exit 126155.6 B.Bennett WRSL
    203 4/07/2019 6:34:00 Enter 126209.5 B.Bennett TDepot
    203 4/07/2019 6:45:00 Exit 126209.6 B.Bennett TDepot
    203 4/07/2019 8:34:00 Enter 126265.1 B.Bennett TDepot
    203 4/07/2019 9:37:00 Exit 126265.3 B.Bennett TDepot
    203 4/07/2019 11:30:00 Enter 126332.4 B.Bennett TDepot
    203 4/07/2019 12:05:00 Exit 126332.5 TDepot
    203 4/07/2019 12:05:00 Enter 126332.6 TDepot
    203 4/07/2019 14:31:00 Exit 126332.8 G.Spedding TDepot
    203 4/07/2019 15:46:00 Enter 126376.2 G.Spedding WRSL
    203 4/07/2019 15:47:00 Exit 126376.5 G.Spedding WRSL
    203 4/07/2019 15:53:00 Enter 126378 G.Spedding WRSL
    203 4/07/2019 16:05:00 Exit 126378.3 G.Spedding WRSL
    204 4/07/2019 7:51:00 Enter 136004.4 J.Tsagaris TDepot
    204 4/07/2019 8:06:00 Exit 136004.6 J.Tsagaris TDepot
    204 4/07/2019 10:20:00 Enter 136059.9 J.Tsagaris TDepot
    204 4/07/2019 11:16:00 Exit 136060.1 J.Tsagaris TDepot
    204 4/07/2019 13:14:00 Enter 136115.2 J.Tsagaris TDepot
    204 4/07/2019 15:39:00 Exit 136115.4 R.Singh TDepot
    204 4/07/2019 17:38:00 Enter 136182.1 R.Singh TDepot
    204 4/07/2019 17:51:00 Exit 136182.1 R.Singh TDepot
    204 4/07/2019 19:04:00 Enter 136226.5 R.Singh WRSL
    204 4/07/2019 19:06:00 Exit 136226.7 R.Singh WRSL
    204 4/07/2019 19:10:00 Enter 136228.3 R.Singh WRSL
    204 4/07/2019 19:10:00 Exit 136228.5 R.Singh WRSL
    205 4/07/2019 9:02:00 Enter 146274.7 G.Spedding TDepot
    205 4/07/2019 9:15:00 Exit 146274.8 G.Spedding TDepot
    205 4/07/2019 11:17:00 Enter 146329.9 G.Spedding TDepot
    205 4/07/2019 11:41:00 Exit 146330 D.Costabile TDepot
    205 4/07/2019 13:29:00 Enter 146396.7 D.Costabile TDepot
    205 4/07/2019 14:10:00 Exit 146396.8 J.Rivera TDepot
    205 4/07/2019 15:56:00 Enter 146463.7 J.Rivera TDepot
    206 4/07/2019 4:06:00 Enter 131307.6 C.Tickner TDepot
    206 4/07/2019 6:05:00 Exit 131307.7 M.Elmahdy TDepot
    207 4/07/2019 6:14:00 Enter 157581.1 TDepot
    207 4/07/2019 7:03:00 Exit 157581.4 P.Nguyen TDepot
    207 4/07/2019 9:19:00 Enter 157651.6 P.Nguyen TDepot
    207 4/07/2019 10:16:00 Exit 157651.8 P.Nguyen TDepot
    207 4/07/2019 12:18:00 Enter 157706.7 P.Nguyen TDepot
    207 4/07/2019 12:50:00 Exit 157707 D.Bowen TDepot
    207 4/07/2019 14:49:00 Enter 157761.7 D.Bowen TDepot
    207 4/07/2019 15:08:00 Exit 157761.9 S.Haidari TDepot
    207 4/07/2019 20:04:00 Enter 157870.4 S.Haidari TDepot
    207 4/07/2019 20:09:00 Exit 157870.5 S.Haidari TDepot
    208 4/07/2019 10:13:00 Enter 157933.9 C.Heritage TDepot
    208 4/07/2019 10:31:00 Exit 157934 C.Heritage TDepot
    209 4/07/2019 7:07:00 Enter 161629.6 S.McNamara TDepot
    209 4/07/2019 7:24:00 Exit 161629.8 S.McNamara TDepot
    209 4/07/2019 9:00:00 Enter 161682.4 S.McNamara WRSL
    209 4/07/2019 9:05:00 Exit 161682.6 S.McNamara WRSL
    209 4/07/2019 10:09:00 Enter 161726.1 S.McNamara TDepot
    209 4/07/2019 10:53:00 Exit 161726.2 J.Armstrong TDepot
    209 4/07/2019 12:45:00 Enter 161781 J.Armstrong TDepot
    209 4/07/2019 14:14:00 Exit 161781.2 K.Metcalfe TDepot
    209 4/07/2019 18:44:00 Enter 161902.4 K.Metcalfe TDepot
    209 4/07/2019 19:33:00 Exit 161902.8 TDepot
    52 4/07/2019 15:39:00 Enter 104657.1 M.Chapman AVA
    52 4/07/2019 18:07:00 Exit 104657.4 M.Chapman AVA
    52 4/07/2019 19:34:00 Enter 104705.1 M.Chapman AVA
    52 4/07/2019 22:03:00 Exit 104705.4 AVA
    53 4/07/2019 10:07:00 Enter 80897.89 S.Gironcel AVA
    53 4/07/2019 10:22:00 Exit 80898.25 S.Gironcel AVA
    53 4/07/2019 11:49:00 Enter 80946.3 S.Gironcel AVA
    53 4/07/2019 11:58:00 Exit 80946.64 S.Gironcel AVA
    53 4/07/2019 14:34:00 Enter 80996.27 M.Smith AVA
    53 4/07/2019 14:36:00 Exit 80996.57 M.Smith AVA
    53 4/07/2019 20:19:00 Enter 81043.73 Z.Poole AVA
    53 4/07/2019 21:10:00 Exit 81044.1 AVA
    54 4/07/2019 7:59:00 Enter 130922.6 N.Coulson AVA
    54 4/07/2019 8:10:00 Exit 130922.9 N.Coulson AVA
    54 4/07/2019 8:16:00 Enter 130925.6 N.Coulson AVA
    54 4/07/2019 9:13:00 Exit 130926 N.Coulson AVA
    60 4/07/2019 6:30:00 Enter 70712.63 T.Natsikas WRSL
    60 4/07/2019 7:05:00 Exit 70713 T.Natsikas WRSL
    60 4/07/2019 8:10:00 Enter 70756.4 T.Natsikas TDepot
    60 4/07/2019 8:25:00 Exit 70756.58 T.Natsikas TDepot
    60 4/07/2019 9:50:00 Enter 70801.05 T.Natsikas WRSL
    60 4/07/2019 9:50:00 Exit 70801.22 T.Natsikas WRSL
    60 4/07/2019 9:54:00 Enter 70802.81 T.Natsikas WRSL
    60 4/07/2019 10:05:00 Exit 70803.05 T.Natsikas WRSL
    60 4/07/2019 11:06:00 Enter 70846.66 T.Natsikas TDepot
    60 4/07/2019 12:30:00 Exit 70846.87 H.Yildiz TDepot
    60 4/07/2019 13:32:00 Enter 70889.98 H.Yildiz WRSL
    60 4/07/2019 13:33:00 Exit 70890.23 H.Yildiz WRSL
    60 4/07/2019 13:40:00 Enter 70891.88 H.Yildiz WRSL
    60 4/07/2019 14:04:00 Exit 70892.04 H.Yildiz WRSL
    60 4/07/2019 15:21:00 Enter 70936.62 H.Yildiz TDepot
    60 4/07/2019 16:26:00 Exit 70936.88 H.Yildiz TDepot
    60 4/07/2019 17:47:00 Enter 70980.72 H.Yildiz WRSL
    60 4/07/2019 17:47:00 Exit 70980.89 H.Yildiz WRSL
    60 4/07/2019 17:51:00 Enter 70982.57 H.Yildiz WRSL
    60 4/07/2019 18:04:00 Exit 70982.72 H.Yildiz WRSL
    60 4/07/2019 19:07:00 Enter 71027.08 H.Yildiz TDepot
    60 4/07/2019 19:56:00 Exit 71027.43 TDepot
    61 4/07/2019 6:17:00 Enter 78398.53 K.Madden WRSL
    61 4/07/2019 6:18:00 Exit 78398.69 K.Madden WRSL
    61 4/07/2019 6:27:00 Enter 78400.3 K.Madden WRSL
    61 4/07/2019 6:35:00 Exit 78400.51 K.Madden WRSL
    61 4/07/2019 9:09:00 Enter 78492.3 K.Madden WRSL
    61 4/07/2019 9:11:00 Exit 78492.53 K.Madden WRSL
    61 4/07/2019 9:16:00 Enter 78494.13 K.Madden WRSL
    61 4/07/2019 9:35:00 Exit 78494.32 K.Madden WRSL
    61 4/07/2019 10:33:00 Enter 78537.76 K.Madden TDepot
    61 4/07/2019 11:00:00 Exit 78537.98 B.Zanca TDepot
    61 4/07/2019 12:20:00 Enter 78590.67 B.Zanca WRSL
    61 4/07/2019 12:35:00 Exit 78590.86 B.Zanca WRSL
    61 4/07/2019 13:31:00 Enter 78644.18 B.Zanca TDepot
    61 4/07/2019 15:03:00 Exit 78644.37 B.Zanca TDepot
    61 4/07/2019 16:07:00 Enter 78697.08 B.Zanca WRSL
    61 4/07/2019 16:35:00 Exit 78697.26 B.Zanca WRSL
    61 4/07/2019 17:42:00 Enter 78749.38 B.Zanca TDepot
    61 4/07/2019 18:23:00 Exit 78749.6 B.Zanca TDepot
    61 4/07/2019 19:26:00 Enter 78793.3 B.Zanca WRSL
    61 4/07/2019 19:27:00 Exit 78793.48 B.Zanca WRSL
    61 4/07/2019 19:33:00 Enter 78794.69 B.Zanca WRSL
    61 4/07/2019 19:33:00 Exit 78794.79 B.Zanca WRSL
    61 4/07/2019 21:36:00 Enter 78837.81 TDepot
    61 4/07/2019 22:32:00 Exit 78837.96 TDepot
    62 4/07/2019 7:16:00 Enter 81367.39 D.Bowen WRSL
    62 4/07/2019 7:17:00 Exit 81367.56 D.Bowen WRSL
    62 4/07/2019 7:25:00 Enter 81369.18 D.Bowen WRSL
    62 4/07/2019 7:36:00 Exit 81369.41 D.Bowen WRSL
    62 4/07/2019 10:08:00 Enter 81456.12 D.Bowen WRSL
    62 4/07/2019 10:09:00 Exit 81456.31 D.Bowen WRSL
    62 4/07/2019 10:13:00 Enter 81457.97 D.Bowen WRSL
    62 4/07/2019 10:35:00 Exit 81458.12 D.Bowen WRSL
    62 4/07/2019 11:40:00 Enter 81501.6 D.Bowen TDepot
    62 4/07/2019 11:50:00 Exit 81501.76 M.Lainata TDepot
    62 4/07/2019 13:00:00 Enter 81544.99 M.Lainata WRSL
    62 4/07/2019 13:03:00 Exit 81545.15 M.Lainata WRSL
    62 4/07/2019 13:07:00 Enter 81545.88 M.Lainata WRSL
    62 4/07/2019 13:34:00 Exit 81546.11 M.Lainata WRSL
    62 4/07/2019 14:40:00 Enter 81589.34 M.Lainata TDepot
    62 4/07/2019 15:09:00 Exit 81589.51 TDepot
    63 4/07/2019 10:53:00 Enter 10059.39 TDepot
    63 4/07/2019 12:24:00 Exit 10059.82 TDepot
    63 4/07/2019 12:32:00 Enter 10064.26 V.DIESEL TDepot
    63 4/07/2019 12:56:00 Exit 10064.4 V.DIESEL TDepot
    63 4/07/2019 12:56:00 Enter 10064.4 V.DIESEL TDepot
    63 4/07/2019 13:04:00 Exit 10064.57 V.DIESEL TDepot
    63 4/07/2019 20:10:00 Enter 10066.25 TDepot
    63 4/07/2019 20:47:00 Exit 10066.39 TDepot
    65 4/07/2019 0:55:00 Enter 94037.31 TDepot
    65 4/07/2019 1:17:00 Exit 94037.34 TDepot
    65 4/07/2019 1:17:00 Enter 94037.43 TDepot
    65 4/07/2019 1:47:00 Exit 94037.56 TDepot
    65 4/07/2019 8:57:00 Enter 94038.52 S.Ristov TDepot
    65 4/07/2019 10:16:00 Exit 94038.78 S.Ristov TDepot
    65 4/07/2019 11:50:00 Enter 94062.32 S.Ristov SXS
    65 4/07/2019 12:00:00 Exit 94063.27 S.Ristov SXS
    65 4/07/2019 12:21:00 Enter 94086.13 S.Ristov TDepot
    65 4/07/2019 12:52:00 Exit 94086.41 T.Chang TDepot
    65 4/07/2019 14:19:00 Enter 94131.03 T.Chang WRSL
    65 4/07/2019 14:35:00 Exit 94131.24 T.Chang WRSL
    65 4/07/2019 15:38:00 Enter 94184.55 T.Chang TDepot
    65 4/07/2019 15:56:00 Exit 94184.74 S.Sheehy TDepot
    65 4/07/2019 17:25:00 Enter 94228.47 S.Sheehy WRSL
    65 4/07/2019 17:25:00 Exit 94228.67 S.Sheehy WRSL
    65 4/07/2019 17:30:00 Enter 94230.33 S.Sheehy WRSL
    65 4/07/2019 17:35:00 Exit 94230.48 S.Sheehy WRSL
    65 4/07/2019 18:54:00 Enter 94282.86 S.Sheehy TDepot
    65 4/07/2019 18:58:00 Exit 94283.03 S.Sheehy TDepot
    Last edited by asad; Jul 4th, 2019 at 08:39 PM.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to make a list depending on the event

    Quote Originally Posted by asad View Post
    Okay,

    But I am unable to get it to work. The line "Dim dict As Dictionary" gets highlighted and gives me an error message - "User-defined type not defined"

    Any ideas how I can fix this?
    To use a Dictionary object you need to first add a reference.

    Tools->References from the Visual Basic menu.
    Find Microsoft Scripting Runtime in the list and place a check in the box beside it.

    Or use late binding;
    Dim dict as Object
    Set dict = CreateObject("Scripting.Dcitionary")

    M.

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    Thanks Marcelo

    The VBA worked perfect now


  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to make a list depending on the event

    You are welcome. Glad to help

    If you are interested...
    Another version (simpler code) that uses late binding (no need to add a reference)
    Worked for me with your data sample above

    Code:
    Sub aTest()
        Dim dict As Object, lastRow As Long, vData As Variant
        Dim i As Long, arrCrit As Variant
        
        'create the dictionary
        Set dict = CreateObject("Scripting.Dictionary")
        'not case sensitive - not necessary in this case b/c the keys are numbers
        dict.CompareMode = vbTextCompare
        
        'get the last row with data
        lastRow = Cells(Rows.Count, "H").End(xlUp).Row
        'pass the data to a variant array to speed up processing
        vData = Range("B2:H" & lastRow)
        
        'loop through vData and add to dictionary if column H = "AVA"
        For i = 1 To UBound(vData, 1)
            If UCase(vData(i, 7)) = "AVA" Then dict(vData(i, 1)) = Empty
        Next i
        'converts the array of numbers (dict keys) to an array of strings
        arrCrit = Split(Join(dict.keys))
        
        With ActiveSheet
            If .FilterMode Then .ShowAllData
            .Range("A1:H" & lastRow).AutoFilter Field:=2, Criteria1:=arrCrit, Operator:=xlFilterValues
        End With
        
    End Sub
    M.

  9. #9
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to make a list depending on the event

    Thanks again Marcelo

    You are very good and helpful.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to make a list depending on the event

    You are very welcome

    M.

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
  •