VBA查找函数查找数组

Question

代码在下面的行中给出“应用程序定义或对象定义的错误”

Set Job_Array = Workbooks("Master Permitting-Test Sheet Copy Code").Worksheets("Sheet1").Range("A5", Range("A5").End(xlDown))

其余代码如下所示。代码应该打开一个Old_Workbook，在单元格A5处启动一个循环，代码在Old_Workbook中接受A5的值&检查New_workbook是否完全匹配。如果找到了新工作簿的匹配项，则代码将从Old_Worksheet 3列中偏移，并复制该单元格，然后在该单元格中偏移New_Workbook中的3列并粘贴该单元格值。然后，代码跳下下一个A6，并为下一个Old_Job_Name重复循环。

Sub Copy_Code()

Dim FileLocation As String
Dim Old_Workbook As Workbook
Dim New_Workbook As Workbook
Dim New_Job_Range As Range
Dim Old_Job_Name As String
Dim Job_Array As Range



Dim n As Integer
Dim Last_Old_Job As Integer


'gets input from user for file string name
FileLocation = Application.GetOpenFilename

'opens last weeks file and sets WB object for new workbook
Set Old_Workbook = Application.Workbooks.Open(FileLocation)
Set New_Workbook = Workbooks("Master Permitting-Test Sheet Copy Code")


'Find the last old job name in the old workbook
Last_Old_Job = Old_Workbook.Worksheets("Sheet1").Range("A5", Range("A5").End(xlDown)).Count



    'Establish loop from the first old job to the last old job
    For n = 1 To Last_Old_Job

        'defines old job name, Starts in cell A5 and steps rows by 1 each time the loop is completed
        Old_Job_Name = Old_Workbook.Worksheets("Sheet1").Range("A5").Offset(n - 1, 0).Value

        'Defines look up array in new workbook
        Set Job_Array = Workbooks("Master Permitting-Test Sheet Copy Code").Worksheets("Sheet1").Range("A5", Range("A5").End(xlDown))

        'Sets the New_Job_Range equal to the cell where the old job matchs the new job
        Set New_Job_Range = Job_Array.Find(Old_Job_Name, LookIn:=xlValues, lookat:=xlWhole)

            'If New_Job_Range is not nothing (as in a match has been found) then offset the column by 3 on the old workbook and paste that data
            'offset 3 columns from the New_Job_Range
            If Not New_Job_Range Is Nothing Then
                Old_Job_Name = Old_Workbook.Worksheets("Sheet1").Range("A5").Offset(n - 1, 3).Copy
                New_Job_Range.Offset(0, 3).Paste


             End If


Next n

End Sub

Answer 1

在设置Job_Array范围之前，您必须插入它，因为Excel在那个时刻将Old_Workbook作为ActiveWorkbook，该公式引用范围(“A5”).End(XlDown)，而没有对该范围进行完全限定的‘路径’。

New_Workbook.Activate