CODEWITHSUNDEEP
excelvba
user1931485
user1931485

Reputation: 25

Custom Excel VBA Function with return type as Date

I have a workbook with multiple sheets. Except for the Summary Sheet, all other sheets have a dynamic range of consecutive cells (within a column) that stores some Date formatted values. I need to select that range dynamically and choose the Largest Date Value. The Function should return that value.

Here's what I was trying:

Function GetMSDate(Counter As Integer, DesignCtrs() As String, NumOfVendors() As Long, 
          MSDesc() As String, PlanOrActual As String) As Date
    GetMSDate = Application.Max(ThisWorkbook.Worksheets(Counter + 1).Range("A:A")
            .Find(PlanOrActual, SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .EntireRow
            .Find(MSDesc(1), SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .Offset(1, 0).Address & ":" & _
            ThisWorkbook.Worksheets(Counter + 1).Range("A:A")
            .Find(PlanOrActual, SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .EntireRow
            .Find(MSDesc(1), SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .Offset(NumOfVendors(Counter) + 1, 0).Address)
    MsgBox GetMSDate
End Function

My intent was that I would define the dynamically constructed Range for the Application.Max() argument. In doing so, finding the range anchor, I choose the correct sheet (Counter+1 allows that correctly); then find a specific "predefined" string in the first column; select the entire row of the found cell and find within that range another predefined string; the range is then defined from the cell below that anchor cell to an offset defined by: NumOfVendors(Counter)+1

In running the above, it gives me:

Type Mismatch error at the "GetMSDate = " statement.

Upvotes: 0

Views: 1326

Answers (1)

Dan Metheus
Dan Metheus

Reputation: 1438

To debug, assign your value to a variant variable and then output the typename() of that variable after the assignment. It should tell you why it is not accepting date.

Function GetMSDate(Counter As Integer, DesignCtrs() As String, NumOfVendors() As Long, MSDesc() As String, PlanOrActual As String) As Date
    Dim v as Variant

    v = Application.Max(ThisWorkbook.Worksheets(Counter +
        1).Range("A:A").Find(PlanOrActual, SearchOrder:=xlByColumns,
        searchDirection:=xlNext).EntireRow.Find(MSDesc(1), SearchOrder:=xlByColumns,
        searchDirection:=xlNext).Offset(1, 0).Address & ":" & _
    ThisWorkbook.Worksheets(Counter + 1).Range("A:A").Find(PlanOrActual,
        SearchOrder:=xlByColumns, searchDirection:=xlNext).EntireRow.Find(MSDesc(1),
        SearchOrder:=xlByColumns, searchDirection:=xlNext).Offset(NumOfVendors(Counter)
        + 1, 0).Address)
    debug.print v
    debug.print TypeName(v)
    stop
    MsgBox GetMSDate
End Function

Upvotes: 1

Related Questions