VBA索引匹配错误 [英] VBA Index Match Error

问题描述

我目前拥有吨/列的产量表（产品名称位于左侧，得分手在顶部，分数位于该范围内的每个单元格中）。我想要做的是创建一个导出/快照，其中只有几行同一工作簿中另一张表上的列。

我试图找出最简单的方式来刷新此工作表中每个单元格的分数，并且到目前为止已经使用Index / Match。我想对其进行编码，使这个过程容易/自动化。理想情况下，我想匹配产品/列名称，以便我可以在出口表上更改订单，金额等。

我一直在尝试这段代码：

  Sub KEY ）
 
 Dim Ratings As Range 
 Set Ratings = Worksheets（EXPORT）。Range（B7：R33）
 Dim iCell As Range 
 
工作表（EXPORT）。激活
对于ActiveSheet.Range（B7：R33）中的每个iCell 
 iCell.Formula = Application.Index（Worksheets（Master Scores）。范围（A1：A500）范围（单元格（iCell.Row，1）），表格（主分数）范围（A1：A500） ））
下一个
 End Sub 
  

我得到运行时错误'1004'：应用程序定义或对象定义的错误

有人可以帮助我吗？我从来没有尝试过使用代码来运行W / VBA的公式。我已经得到一个常规的索引匹配来粘贴到每个单元格中，但是要保留我创建的iCell变量，所以我可以通过行/列名称引用，如果这是有道理的。

如果有更好的方法来做我想要完成的工作，请让我知道 - 我还没有找到一个。

解决方案

99％的时间1004错误是因为您有大量定义的范围对象。

记住，只要您不将范围对象限定为其父 Sheet ，编译器将默认假定该范围属于 ActiveSheet 。当您尝试在其他工作表中定义一个范围时，这是特别有问题的。

例如：如果Sheet1处于活动状态，这将提高一个错误：

 工作表（Sheet2）。范围（单元格（1,1），单元格（1,2））你可以肯定这是一个潜在的错误：
 
 $ b 
 工作表（Master Scores）。范围（Cells.Find（iCell.Value）.EntireColumn 
  
这将引发错误，因为其他工作表被激活：
 工作表（EXPORT）激活
  
有两种方法可以解决这个问题：基本的方法do 不是建议简单地说明哪个表格是活跃的，并且完全符合所有内容，但这是一个痛苦的屁股，并使草率，难以辨认的代码参见：
 
 
 
 如何避免使用Excel中的选择VBA宏 
 
 
 
另一种解决方法是符合条件你的范围适当。对于简单的情况，您可以使用With块：
 使用Worksheets（Master Scores）
 iCell.Formula = .Range（.Cells.Find（iCell.Value）... 
 
结束
  
但是由于您至少有两个不同的表格引用，这是不可能的，您必须定义一些范围变量是合格的，并使用那些代替复杂连接的范围变量你正在努力。
  Dim INDEX_ARRAY As Range 
 Dim INDEX_COLUMN As Range 
 Dim INDEX_ROW As范围
 
与工作表（主分数）
设置INDEX_ARRAY = .Range（.Cells.Find（iCell.Value）.EntireColumn））
设置INDEX_COLUMN = .Range A1：A500））
结束与
 
与工作表（EXPORT）
设置INDEX_ROW = .Range（.Cells（iCell.Row，1））
结束$ th 
  
然后你可以做：
  iCell.Formula = Application.Index（INDEX_ARRAY，INDEX_ROW， INDEX_COLUMN）
  
 另请注意 
 
 
 
您正在使用公式字符串中的 .Find 方法和 .Match 函数。如果其中任何一个导致错误，则整个语句将出错。我建议你调试这些，考虑分别评估每个部分，检查错误，然后只有一旦确保它不会错误，然后构建 .Formula 的字符串。 / p> 
I currently have a score sheet with tons of rows/columns (product names are on the left, scorers are on the top, and scores are in each cell within this range). What I'm trying to do is create an export/snapshot of only a few of those rows & columns on another sheet in the same workbook. 

I'm trying to figure out the easiest way to refresh the scores in each of the cells on this worksheet, and so far have arrived at using Index/Match. I want to code it to make this process easy/automated. Ideally, I'd like to match off of product/column names so that I can change the order, amount, etc on the export sheet. 

I've been trying this code: 
Sub KEY()

Dim Ratings As Range
Set Ratings = Worksheets("EXPORT").Range("B7:R33")
Dim iCell As Range

Worksheets("EXPORT").Activate
For Each iCell In ActiveSheet.Range("B7:R33")
    iCell.Formula = Application.Index(Worksheets("Master Scores").Range(Cells.Find(iCell.Value).EntireColumn), Application.Match(Sheets("EXPORT").Range(Cells(iCell.Row, 1)), Sheets("Master Scores").Range("A1:A500")))
    Next
End Sub
And am getting "Run-time error '1004': Application-defined or object-defined error"

Can someone help me out with this? I've never tried to use code to run formulas w/ VBA before. I've gotten a regular Index Match to paste into each of the cells, but want to preserve the "iCell" variable I've created so I can reference by row/column name if that makes sense.

If there is a better way to do what I'm trying to accomplish, please let me know- I just haven't found one as of yet.
解决方案
99% of the time the 1004 error is because you have sloppily defined range objects.

REMEMBER that whenever you do not qualify a range object to its parent Sheet, the compiler will default to assume that this range belongs to the ActiveSheet.  This is particularly problematic when you are attempting to define a range on another sheet, when that other sheet is not active.

E.g.,: if Sheet1 is active, this will raise an error:
Worksheets("Sheet2").Range(Cells(1,1),Cells(1,2))._ANY_METHOD_
You certainly have that as a potential error:
Worksheets("Master Scores").Range(Cells.Find(iCell.Value).EntireColumn
This will raise an error, because the other sheet is activated by:
Worksheets("EXPORT").Activate
There are two ways to resolve this: the basic way I do not recommend is to tediously keep track of which sheet is "Active" and fully qualify everything. But that is a pain in the butt and makes for sloppy, illegible code.  See also:

How to avoid using Select in Excel VBA macros

The other way to resolve this is to qualify your ranges appropriately. For simple cases, you can use a With block:
With Worksheets("Master Scores")
    iCell.Formula = .Range(.Cells.Find(iCell.Value)...

End With
But since you have at least two different sheet references, that will not be possible.  You have to define some range variables that are qualified, and use those in lieu of the complicated concatenation that you're trying to do.
Dim INDEX_ARRAY As Range
Dim INDEX_COLUMN As Range
Dim INDEX_ROW As Range

With Worksheets("Master Scores")
    Set INDEX_ARRAY = .Range(.Cells.Find(iCell.Value).EntireColumn))
    Set INDEX_COLUMN = .Range("A1:A500"))
End With

With Worksheets("EXPORT")
    Set INDEX_ROW = .Range(.Cells(iCell.Row,1))
End WIth
And then you can do:
iCell.Formula = Application.Index(INDEX_ARRAY, INDEX_ROW, INDEX_COLUMN)
NOTE ALSO

You are using the .Find method and the .Match function within your formula string. If either of those results in an error, the entire statement will error. I suggest you debug those, consider evaluating each piece separately, checking for errors, and then build the string for the .Formula only once you have ensured that it will not error.

                        
这篇关于VBA索引匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！