Excel vba: type mismatch error on Array date format-CodePudding

Can someone help me on this error please..

getting type mismatch error on Array date format in the line mentioned below. mykey = arr(i, 7) & format(arr(i, 11), "|dd-mmm-yy") 'job name & start date

Please help me on this. Code:

     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

Getting the issue in another line.. could you please help..

CodePudding user response：

I recreated the table and ran your macro. It compiles just fine and writes result to column 'Final Status'.

Looks like the error on line

mykey = arr(i, 7) & format(arr(i, 11), "|dd-mmm-yy")

could be due to formatting issue. In table column 7 (arr(i, 7)) I set the format to "Time". Your picture only shows 9 columns, but set your format in column 11 to "Short Date", it worked for me.