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

we do not typically put the tags associated with a question in the title as well. — , May 21 '15 at 01:43
Did you find posted here anything useful? Please post feedback. — sancho.s ReinstateMonicaCellio, Jul 05 '15 at 10:10

sancho.s ReinstateMonicaCellio · Answer 1 · 2015-07-05T10:09:25.047

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).

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

score 2 · Accepted Answer · answered May 21 '15 at 01:35

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

Works like a charm! Thanks. Problem Solved. What am expert you are and what a great community this is. Kudos to you and everyone in this community helping each others. I don;t have enough reputation to upvote you or I would. — Manish Jain, May 21 '15 at 02:52
@R3uK - Not moving the *numAssignments* value assignment into the With/End With block was an intentional 'Easter Egg' left for the OP. And yes, I should have included a link to [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — , May 21 '15 at 23:15

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

2 Answers2

Linked