Finding Max Date from "Find" Range

Question

I have a variety of calibration tests. I keep all different types and their dates in one worksheet "wsCAL"

I want to populate the userform with the most recent date of one specific type of test, which is stored in Column C in wsCAL.

In theory, I want VBA to go to wsCAL, look thru column C and find all instances of one test type, find the most recent date(or MAX) in column B of those instances, then populate my userform with that date.

I've tried using the rangeCAL = .Find() function to find all instances of a test type in column C. This part works just fine. However, the application.worksheetfunction.Max(rangeCAL) I try to use fails. I'm guessing it is because that application function only works with worksheet ranges and not Find() ones. I'm struggling with taking my rangeCAL cells, making an array, then finding the most recent date (the MAX) of those.

Private Sub UserForm_Initialize() 'Upon opening the userform

    Set wb = ThisWorkbook
    Set wsHOME = wb.Worksheets("Home")
    Set wsCAL = wb.Worksheets("Bottle Calibrations")
    Set wsC1T1 = wb.Worksheets("C1T1")

    'Last Calibration Date      
        Label27.Caption = vbNullString
        With wsCAL
        Dim Cell As Range
        Dim myArray As Date
        Dim i As Integer
        Dim rangeCAL As Range
        Dim rangeDateCAL As Date

        i = 0
        Set rangeCAL = Range("C:C").Find(What:=tank, LookAt:=xlWhole)
            If Not rangeCAL Is Nothing Then
                For Each Cell In rangeCAL
                    myArray(i) = .Range(rangeCAL.Row, "A").Value
                    i = i + 1
                Next
            Else
                MsgBox "Error: no previous Calibration dates loaded."
            End If
            rangeDateCAL = Application.WorksheetFunction.Max(myArray)
            rangeDateCAL = Format(rangeDateCAL, "yymmdd")

    End With
    Label27.Caption = rangeDateCAL

I keep getting the error message

"Expected array"

as soon as I get to line:

myArray(i) = .Range(rangeCAL.Row, "B").Value

UPDATE:

Label27.Caption = vbNullString
With wsCAL
    Dim Cell As Range
    Dim myArray(1 To 5) As Date
    Dim i As Long
    Dim temp As Date
    Dim rangeCAL As Range
    Dim rangeDateCAL As Date

    i = 1
    Set rangeCAL = wsCAL.Range("C1", Range("C1").End(xlDown).Address)
        For Each Cell In rangeCAL
            If Cell <> "" Then
                If Cell.Value = tank Then
                    temp = wsCAL.Cells(Cell.Row, "B").Value
                    myArray(i) = temp
                    i = i + 1

                End If
            End If
        Next

    rangeDateCAL = Application.WorksheetFunction.Max(myArray)
    rangeDateCAL = Format(rangeDateCAL, "yymmdd")

End With

    Label27.Caption = rangeDateCAL

I implemented this change after reading your comments. This code runs, but it fills Label27.Caption with 11/22/4613 instead of the intended 11/7/2019.

I'm assuming the date value is being altered at the MAX function step, but I'm not sure what else I can change.

Answer 1

For Each Cell In rangeCAL
    If Cell.Text <> vbNullString Then
        If Cell.Text = tank Then 'assuming tank is declared a string
            If tempDate < wsCAL.Cells(Cell.Row, "B").Value Then
                tempDate = wsCAL.Cells(Cell.Row, "B").Value
            End If
        End If
    End If
Next

Label27.Caption = Format(tempDate, "yymmdd")

Answer 2

This is what I implemented, per SmileyFTW's suggestion. Far simpler than anticipated. Works as intended though. Thank you SmileyFTW, and the others who commented with help.

Label27.Caption = vbNullString
With wsCAL
    Dim Cell As Range
    Dim i As Date
    Dim temp As Date
    Dim rangeCAL As Range

    temp = 0
    Set rangeCAL = wsCAL.Range("C1", Range("C1").End(xlDown).Address)
        For Each Cell In rangeCAL
            If Cell <> vbNullString Then
                If Cell.Value = tank Then
                    i = wsCAL.Cells(Cell.Row, "B").Text
                    If i > temp Then
                        temp = i
                    End If

                End If
            End If
        Next
End With
Label27.Caption = temp