Dim x, y(), i&, j&, k&, l&, s$
x = Range("C1:I" & Cells(Rows.Count, 3).End(xlUp).Row).Value
ReDim y(1 To UBound(x), 1 To 5)
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(x)
If x(i, 4) = "Overtime" Or x(i, 4) = "Overtime On Saturday" Or x(i, 4) = "Overtime On Sunday" Or x(i, 4) = "Saturday Premium" Or x(i, 4) = "Sunday Premium" Or x(i, 4) = "Travel" Or x(i, 4) = "Travel Saturday" Or x(i, 4) = "Travel Sunday/Holiday" Then
s = x(i, 2) & x(i, 4) 'BEMSID&HOURS_DESCR is the unique key
'if key already exists in the dictionary, we sum AMOUNT
If .exists(s) Then
k = .Item(s): y(k, 5) = y(k, 5) + x(i, 7) 'y(k, 5) is 'AMOUNT'
Else 'if key not exists
j = j + 1: .Item(s) = j 'add key in the dictionary,
' and fill the output array
y(j, 1) = x(i, 2): y(j, 2) = x(i, 1) 'column BEMSID
y(j, 2) = Split(x(i, 1), ",")(0) 'column EMPLOYEE, surname only
y(j, 3) = x(i, 4): y(j, 4) = x(i, 7) 'column HOURS_DESCR
y(j, 4) = "" 'column Value,
y(j, 5) = x(i, 7) 'column AMOUNT (Units)
End If
End If
Next i
End With
If j = 0 Then Exit Sub
So this is my code as above, but what it there is nothing in the data when I open the file ? It doesn't do anything apart from open the data file to extract the info from so how can I put something in here to say if no results obtained then have a message box appear to say "no data to import this month" and to exit the sub....
Bookmarks