You are welcome, and thank you for the reps.
I have amend the code, to make the cells with contents like numeric 1862 is the same as string 1862 :
Sub Test()
Dim arrIn, arrOut, startDate As Date, endDate As Date, i As Long, j As Long, p As Long
arrIn = Sheet1.Range("A4").CurrentRegion.Resize(, 10).Value
With Sheet2
startDate = .Range("A2").Value
endDate = .Range("E2").Value
With .Range("A4").CurrentRegion.Resize(, 8)
.Offset(1, 1).ClearContents
arrOut = .Value
End With
For p = 2 To UBound(arrOut, 1)
For i = 2 To UBound(arrIn, 1)
If CStr(arrOut(p, 1)) = CStr(arrIn(i, 3)) Then 'Check same "Code"
If arrIn(i, 2) >= startDate And arrIn(i, 2) <= endDate Then 'Check between Start and End Date
arrOut(p, 8) = arrOut(p, 8) + arrIn(i, 10) 'Calculation for "Time"
For j = 2 To 7 'Calculation for "Sector"; 2 = Column(AR), 7 = Column(RT)
If arrIn(i, 7) = arrOut(1, j) Then
arrOut(p, j) = arrOut(p, j) + 1
Exit For
End If
Next j
End If
End If
Next i
Next p
.Range("A4").Resize(UBound(arrOut, 1), UBound(arrOut, 2)) = arrOut
End With
End Sub
This is my version using Excel formulas :
Formula on B5 :
Formula:
=COUNTIFS(Sheet1!$C$5:$C$12,Sheet2!$A5,Sheet1!$G$5:$G$12,Sheet2!B$4,Sheet1!$B$5:$B$12,">="&Sheet2!$A$2,Sheet1!$B$5:$B$12,"<="&Sheet2!$E$2)
and to be copied to B5:G10
Formula on H5 :
Formula:
=SUMIFS(Sheet1!J$5:J$12,Sheet1!C$5:C$12,Sheet2!A5,Sheet1!B$5:B$12,">="&Sheet2!$A$2,Sheet1!B$5:B$12,"<="&Sheet2!$E$2)
and to be copied to H5:H10
Originally Posted by
YasserKhalil
Mr. Karedog
I tested the code .. after trying to run it again I have noticed that it clears the contents of the range B5:G10
See the attachment
Yes, the cells must be cleared first, or there will be non zero initial values for arrOut
Originally Posted by
YasserKhalil
Is there a way to devise UDF for this mission
Since the results is span across multicell, it means that the UDF is returning array, which is known to be slow.
Regards
Bookmarks