Find max and min date in a range of another sheet

Question

I am writing a VBA code to find the minimum and maximum dates in a Range. When I execute it, I get an error:

Run-time error '1004': Application-defined or object-oriented error.

Below is my code:

Sub GenerateSheet()

    Dim i, r, numAssignments As Integer
    Dim ssrRng, DestRange As Range
    Dim StartDate, EndDate, d As Date

    numAssignments = Sheets("Data").Range("A1048576").End(xlUp).Row - 1
    Sheets("Schedule").Select

    EndDate = WorksheetFunction.Max(Sheets("Data").Range(Cells(2, 8), Cells(numAssignments, 8)))
    StartDate = WorksheetFunction.Min(Sheets("Data").Range(Cells(2, 5), Cells(numAssignments, 5)))

End Sub

Here, Data Sheet has 8 columns, Column 5 and 8 are dates

Answer 1

You better change a few aspects of your code, despite not all of them being responsible for the error you get. They make, in general, your code more prone to errors (for instance, when changing code or applying it to other cases).

1. Use of Dim: Dim ssrRng, DestRange As Range declares ssrRng as Variant, and DestRange as Range. You should use Dim ssrRng As Range, DestRange As Range, assuming you want both as ranges.

2. Use variables instead of explicit references, in particular if those are repeated. Use
   Dim ws as Worksheet
Set ws = Workbooks(<your workbook name>).Sheets("Data")
numAssignments = ws...
   instead of
   numAssignments = Sheets("Data")...

3. Fully qualify the ranges you use, unless you explicitly do not want that.

   • Replace
     numAssignments = Sheets("Data")... with, e.g.,
     numAssignments = Workbooks(<your workbook name>).Sheets("Data")...
     (or, better, follow point 2, which already considers this point).
   • Replace
     EndDate = WorksheetFunction.Max(Sheets("Data").Range(Cells(2, 8), Cells(numAssignments, 8))) with
     EndDate = WorksheetFunction.Max(ws.Range(ws.Cells(2, 8), ws.Cells(numAssignments, 8)))
     Likewise for StartDate. In this case, these lines were the source of error, since Cells without qualifier works in the ActiveSheet.

4. Avoid using Select, unless you explicitly need it. Declare and Set variables, and use them for referencing Ranges or Objects you want to work with.

Answer 2

You are telling range that its parent is Sheets("Data") but not cells. For all intents and purposes you wanted a range from Data!E2:Schedule!E99.

Sub GenerateSheet()

    Dim i, r, numAssignments As Integer
    Dim ssrRng, DestRange As Range
    Dim StartDate, EndDate, d As Date

    numAssignments = Sheets("Data").Range("A1048576").End(xlUp).Row - 1
    Sheets("Schedule").Select

    with Sheets("Data")
        EndDate = WorksheetFunction.Max(.Range(.Cells(2, 8), .Cells(numAssignments, 8)))
        StartDate = WorksheetFunction.Min(.Range(.Cells(2, 5), .Cells(numAssignments, 5)))
    end with

End Sub

Using the With Sheets("Data") tells everything inside that block that is prefaced with a period (aka . or full stop) that its parent is Sheets("Data").