Hi,
This piece of code is only pulling the latest / maximum date if multiple criteria's match. Instead of just pulling the maximum date, I want to pull the Large date by adding a condition, i.e. if the offset column "rCell.Offset(0, 14).Value" is empty, pull the Large date "fCell.Offset(0, 9).Value = WorksheetFunction.Large(rCell.Offset(0, 11).Value, 3)" , otherwise the latest / maximum date.
Thank You.
Code
For Each fCell In xFac
If fCell.Offset(0, 3).Value = "CC" And fCell.Value <> "" Then
xCriteria(1) = fCell.Value
xCriteria(2) = fCell.Offset(0, 1)
xCriteria(3) = fCell.Offset(0, 2)
With CreateObject("scripting.Dictionary")
For Each rCell In xRRpt
If rCell.Value = xCriteria(1) And rCell.Offset(0, 1).Value = xCriteria(2) And rCell.Offset(0, 2).Value = xCriteria(3) Then 'And rCell.Offset(0, 3).Value = xCriteria(4) Then 'And rCell.Offset(0, 2).Value = xCriteria(4) Then
s = rCell.Offset(0, 11).Value & rCell.Value & rCell.Offset(0, 1).Value & rCell.Offset(0, 2).Value '& rCell.Offset(0, 14).Value
If .exists(s) Then
fCell.Offset(0, 9).Value = WorksheetFunction.Max(rCell.Offset(0, 11).Value, .Item(s))
.Item(s) = WorksheetFunction.Max(rCell.Offset(0, 11).Value, .Item(s))
Else
fCell.Offset(0, 9).Value = rCell.Offset(0, 11).Value
.Add s, rCell.Offset(0, 11).Value
End If
End If
Next rCell
End With
End If
Next fCell