vba - How to find max and min date in a range of another sheet?

Question

I am writing 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.