Hi,
With THANKING YOU in advance,
I'm running into some troubles in writing this Macro Function in Excel VB. 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 GetMSDateEnd 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)+1In running the above, it gives me:
[INDENT]Type Mismatch error at the "GetMSDate = " statement.
[/INDENT]