Type Mismatch error for the dictionary key

[bobbyexcel]

Getting type mismatch error on the below code for the following data..

Dim T_Start, T_Stop, Shift_Start, Shift_Stop, Result    
     Set dict = CreateObject("scripting.dictionary")
     Set lo = Sheets("temp_sheet").ListObjects("TBL_Jobs")   
     arr = lo.DataBodyRange.Value2     'read that table to an array
     ReDim Result(1 To UBound(arr), 1 To 1)


     '1st ROUND : find last status at the end of the shift
    For i = 1 To UBound(arr)     'loop through data
            T_Start = arr(i, 11) + arr(i, 12)     'timestamp end of job
            T_Stop = arr(i, 14) + arr(i, 15)     'timestamp end of job
          
            mykey = arr(i, 7) & format(arr(i, 11), "\|dd-mmm-yy")     'job name & start date
          
        If arr(i, 11) = arr(i, 14) Then
          
            If T_Stop <= arr(i, 11) + TimeSerial(15, 0, 0) Then     'job must end before next day 3PM
               If Not dict.exists(mykey) Then
                    dict(mykey) = Array(T_Stop, arr(i, 10))
               Else
                    If dict(mykey)(0) < T_Stop Then dict(mykey) = Array(T_Stop, arr(i, 10))     '---> for that job and that startdate, the last endmoment & status
               End If
            Else
               Result(i, 1) = "Notwithinshift"
          End If
     
                    
          Else
          
            If T_Stop <= arr(i, 11) + 1 + TimeSerial(15, 0, 0) Then     'job must end before next day 3PM
                 If Not dict.exists(mykey) Then
                      dict(mykey) = Array(T_Stop, arr(i, 10))
                 Else
                      If dict(mykey)(0) < T_Stop Then dict(mykey) = Array(T_Stop, arr(i, 10))     '---> for that job and that startdate, the last endmoment & status
                 End If
            Else
                 Result(i, 1) = "Notwithinshift"
            End If
     
     
        End If
     
    Next

     '2nd ROUND : add status corresponding with status "end of shift"
     For i = 1 To UBound(arr)     'loop through data
          If Len(Result(i, 1)) = 0 Then     'no blocking conditions
            mykey = arr(i, 1) & format(arr(i, 11), "\|dd-mmm-yy")    'key within dictionary
            Result(i, 1) = dict(mykey)(1)     'last known status
          End If
     Next

     lo.ListColumns("Final Status").DataBodyRange.Value = Result     'write array to listobject

End Sub

 

[Fluff]

Please stop duplicating your questions. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

Thread closed.