How to find the maximum and minimum date in the 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 message:

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, the data table has 8 columns, columns 5 and 8 are dates

Answer 1

It is better to change several aspects of your code, although not all of them are responsible for the error received. In general, your code is more prone to errors (for example, when changing the code or applying it in other cases).

• Dim: Dim ssrRng, DestRange As Range ssrRng Variant DestRange Range. Dim ssrRng As Range, DestRange As Range, , .

• , , .
  Dim ws as Worksheet
Set ws = Workbooks(<your workbook name>).Sheets("Data")
numAssignments = ws...

numAssignments = Sheets("Data")...

• , , .

  • 
    numAssignments = Sheets("Data")... , ,
    numAssignments = Workbooks(<your workbook name>).Sheets("Data")...
    (, , 2, ).
  • 
    EndDate = WorksheetFunction.Max(Sheets("Data").Range(Cells(2, 8), Cells(numAssignments, 8)))
EndDate = WorksheetFunction.Max(ws.Range(ws.Cells(2, 8), ws.Cells(numAssignments, 8))) StartDate. , Cells ActiveSheet.

• Select, . Range Object, .

Answer 2

, ( "" ), . 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

With Sheets("Data") , (aka . ), ( "" ).