Excel VBA - How to use application.max with a string of UK dates? e.g. get the most recent date. 1

[knifey]

Hi,

I have a sub that extracts selected (non-contiguous) dates from a very large worksheet (based on another column). I then need to compare these dates to one another to find the most recent date.

I have copied a snapshot of my code below. My problem is I get a type mismatch error on the line:
mostRecentDate = Excel.Application.Max(CDate(dateStr))

I know this has to do with a variable that has the wrong data type. But whatever data type I try either gives a type mismatch or 2015 error (or an incorrect result).

Any help or advice as to where I'm going wrong would be much appreciated.

Best regards,
K

Dim wb2CoaRecdDate As Variant
Dim dateStr As String
Dim arrdates() As String
Dim mostRecentDate As Date

'find batch no. in file
With wb2.Worksheets("sheet1").Columns("C").Rows("3:" & endRow)
    Set f3 = .Find(wb1BatchNumber, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
    'if it is found then..
    If Not f3 Is Nothing Then
        'where was it found
        foundCol = f3.Column
        foundColLetter = colNo2ColRef(foundCol)
        firstAddress = f3.Address
        
        Do
            'search for dupe batch numbers and get dates into array
            wb2CoaRecdDate = wb2.Worksheets("Sheet1").Columns("H").Rows(f3.Row).Value
            
            If wb2CoaRecdDate = vbNullString Or _
                wb2CoaRecdDate = "00:00:00" Or _
                wb2CoaRecdDate = "1/0/1900" Or _
                IsEmpty(wb2CoaRecdDate) Then
                Set f3 = .FindNext(f3)
            Else
                If wb2CoaRecdDate Like "##/##/####" Or _
                    wb2CoaRecdDate Like "##/##/##" Then
                    wb2CoaRecdDate = Format(wb2CoaRecdDate, "yyyy-mm-dd")
                    'wb2CoaRecdDate = UKDate(wb2CoaRecdDate)
                    If dateStr = "" Then
                        dateStr = wb2CoaRecdDate
                    Else
                        dateStr = dateStr & "," & wb2CoaRecdDate
                    End If
                    i1 = i1 + 1
                    Set f3 = .FindNext(f3)
                End If
            End If

        'loop while something is found, unless it was the first thing found
        Loop While Not f3 Is Nothing And f3.Address <> firstAddress

        If i1 = 1 Then
            mostRecentDate = dateStr
        ElseIf i1 = 0 Then
            'do nothing
        Else
            mostRecentDate = Excel.Application.Max(CDate(dateStr))
            If mostRecentDate <> "" Then
                wb1.Worksheets("master").Columns("N").Rows(f3.Row).Value = mostRecentDate
            End If
        End If
    End If
End With

 

[SkipVought]

hi,

This code is nice but largely irrelevent.

The specific question resolves around [highlight]the value of dateStr[/highlight]
[tt]
mostRecentDate = Excel.Application.Max(CDate(dateStr))
[/tt]
Please post [highlight]THAT[/highlight] value that you're trying to get a MAX of.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Probably more efficient to Filter and Subtotal, something like:

[blue]Public Function GetMaxDate() As Date
    Application.ScreenUpdating = False
    ActiveSheet.Range("$C$2:$C$" & endrow).AutoFilter Field:=1, Criteria1:=wb1BatchNumber
    GetMaxDate = Excel.Application.Subtotal(4, ActiveSheet.Range("$H$2:$H$" & endrow))
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Function[/blue]

 

[SkipVought]

If you are doing nothing more than finding the MAX date value, (which REALLY is a NUMBER, BTW!!!) Just compare the current value with the current max value and replace if current value is greter than the current max value.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[knifey]

Hi Skip,

The value of dateStr is 2014-01-02,2014-01-03

K

 

[SkipVought]

how about my previous post's suggestion?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[knifey]

Hi Skip,

I'm not sure I understand. The current max value is what I'm trying to get?
Please can you elaborate.

Cheers,
K

 

[SkipVought]

at each pass thru the loop, you can find a MAX with respect to the previous pass MAX

do
  if MxDate < wb2CoaRecdDate then
     MxDate = wb2CoaRecdDate
  end if
loop

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[knifey]

Thanks Skip, you're a star.
All the best,
Roy