VBA:我收到以下错误:运行时错误"1004"应用程序定义或对象定义的错误 [英] VBA: I get the following error: run-time error '1004' Application-defined or object-defined error

问题描述

当前问题:

我的代码曾经可以部分工作，直到我向sheet2添加额外的数据为止，现在它失败了并且调试将我发送到:

My code used to work partially until I add extra data to sheet2, now when it fails and debug sends me to:

Set partsheet = Worksheets("Sheet1").Range("A1", Range("A65536").End(xlUp)) 

此代码的实用性:

我基本上在不同的工作表中有两列:我想比较&当导入数据相似时:

I basically have two columns in different sheets: that I want to compare & when similar import data:

工作表1-列1是工作编号

Sheet1 - column1 is a job number

Sheet1-column2是零件编号

Sheet1 - column2 is a part number

对于相同的职位编号，您可以将其分为多个部分-表示职位编号会在第1列中重复出现

for the same job number you can have multiple parts - meaning the job number repeats itself in column1

工作表2-列1与工作表1相同，但数据永不重复

Sheet2 - column1 is the same job numbers as sheet1 but the data is never repeat

工作表2-列2是每个工作编号专用的ID号(因此，在这种情况下，工作编号不重复的原因)

Sheet2 - column2 is an id number specific to each job number (hence why in this case job numbers do not repeat themselves)

因此，我想使用公用作业号作为参考点从Sheet2-column2导入数据.当我将数据导入Sheet1-column3时，唯一的ID号必须针对重复的column1(sheet1)中的作业编号重复其自身，因此将出现双重for循环.

Therefore I want to import the data from Sheet2 - column2 using the commun job numbers as reference point. When I import the data to Sheet1 - column3 the unique id number will have to repeat itself for job numbers in column1(sheet1) that are repeated - hence the double for loop.

我的意思是指部分工作:

从代码开始只能部分工作，我认为这是由于以下事实:工作表1-列1中的某些作业编号在工作表2-列1中没有对应的值(数据并不完美).也许我需要为if语句的else部分添加代码，但不确定是什么.

To start with the code only partially worked, I think this was due to the fact that certain job numbers in sheet1 - column1 have no corresponding value in sheet2 - column1 (the data isnt perfect). Maybe I need to add code for the else part of the if statement but im not sure what.

vba代码:

Option Explicit

Sub testFil()
    Dim Jobref_in_partsheet As Range
    Dim Jobref_in_jobsheet As Range
    Dim partsheet As Range
    Dim jobsheet As Range
    Dim MyRow As Long
    Dim MyOtherRow As Long

    Set partsheet = Worksheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
    Set jobsheet  = Worksheets("Sheet2").Range("A1", Range("A65536").End(xlUp))

    For Each Jobref_in_partsheet In partsheet
        For Each Jobref_in_jobsheet In jobsheet
            If Jobref_in_partsheet.Value = Jobref_in_jobsheet.Value Then
                MyRow = Jobref_in_partsheet.Row
                MyOtherRow = Jobref_in_jobsheet.Row
                Worksheets("Sheet1").Cells(MyRow, 3) = Worksheets("Sheet2").Cells(MyOtherRow, 2)
            Else
                 'Do nothing
            End If
        Next
    Next
End Sub

我非常感谢我能得到的任何帮助.

I'd really appreciate any help I could get.

推荐答案

Worksheets("Sheet1").Range("A1", Range("A65536").End(xlUp))

这里有一个问题:您正在尝试创建一个 Range 对象，其第一个单元格是 Sheet1 的 A1 ，并且last cell是活动工作表的 A 列中使用的最后一个单元格.可以猜到， Sheet1 和活动工作表可以是不同的工作表，并且不能创建属于多个工作表的范围.

There is a problem here: you are trying to create a Range object, first cell of which is A1 of the Sheet1, and last cell is the last cell used in column A in the active sheet. As you can guess, Sheet1 and active sheet can be different sheets, and you can't create a range that belongs multiple sheets.

您应该为最后一个单元格明确提供父表:

You should provide the parent sheet explicitly for your last cell:

Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A65536").End(xlUp))

使用 .Address 并不是真正的解决方法，因为它可能导致获取地址从一张纸用于另一张纸.

Using .Address is not really a workaround, as it might result in address fetched from one sheet to be used against another sheet.

这篇关于VBA:我收到以下错误:运行时错误"1004"应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！