View Single Post
  #5   Report Post  
Old September 5th 19, 05:24 PM posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,763
Default Reporting the maximum date within rows with the same keydata

Hi again,

Am Thu, 5 Sep 2019 18:22:52 +0200 schrieb Claus Busch:


Sub Test()

there's one superfluous line in the code.

Sub Test()
Dim LRow As Long, i As Long, First As Long, Cnt As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object
Dim rngD As Range, rngO As Range
Dim myMax As Date

Set myDic = CreateObject("Scripting.Dictionary")
With ActiveSheet
LRow = .Cells(.Rows.Count, "H").End(xlUp).Row
varData = .Range("H2:H" & LRow)
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
varTmp = myDic.items
For i = LBound(varTmp) To UBound(varTmp)
First = Application.Match(varTmp(i), .Range("H:H"), 0)
Cnt = Application.CountIf(.Range("H:H"), varTmp(i))
Set rngD = .Cells(First, "EY").Resize(Cnt)
myMax = Application.Max(rngD)
Set rngO = .Cells(First, "FK").Resize(Cnt)
If myMax 0 Then
rngO.Value = myMax
End If
End With
End Sub

Claus B.
Office 2016