在另一个表的范围内找到max和min日期

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

我正在编写一个VBA代码来查找一个范围内的最小和最大日期。当我执行它时，我得到一个错误:

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

运行时错误'1004':应用程序定义的或面向对象的错误。

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

在这里，数据表有8个列，第5列和第8列是日期。

2 个解决方案

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

   使用Dim ssrRng, DestRange As Range声明ssrRng为变体，DestRange As Range。您应该使用Dim ssrRng作为范围，DestRange作为范围，假设您希望两者都作为范围。

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

   使用变量而不是显式引用，特别是当这些引用重复时。使用Dim ws作为工作表集ws =工作簿( <您的工作簿名称> ).Sheets("Data") numAssignments = ws…而不是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).
   • 取代numAssignments =表(“数据”)……例如，numAssignments = Workbooks( ). sheets(“Data”)…(或者，更好的做法是遵循第2点，它已经考虑到了这一点)。
   • 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.
   • 取代EndDate = WorksheetFunction.Max(表(“数据”)。范围(单元格(2,8)，单元格(numAssignments, 8))和EndDate = WorksheetFunction.Max(ws.Range(ws)。细胞(2 8)ws。单元格(numAssignments, 8))同样适用于StartDate。在本例中，这些行是错误的来源，因为没有限定符的单元格在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.

   避免使用Select，除非您明确地需要它。声明和设置变量，并将其用于引用要处理的范围或对象。

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

您正在告诉范围，它的父元素是Sheets(“Data”)，而不是单元格。对于所有的意图和目的，你想要一个范围从数据!

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

使用With Sheets(“Data”)可以告诉块中以句点(aka)开头的所有内容。或者完全停止，它的父元素是Sheets(“Data”)。