VBA索引匹配错误(VBA Index Match Error)

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.

Answer 1:

当时的1004错误99％是因为你拖泥带水定义范围内的对象。

请记住 ，只要你没有资格的范围对象到其父Sheet ，编译器将默认的假设，这个范围属于ActiveSheet 。 这是当你正在试图定义在另一片的范围内，当另一片不活跃尤其成问题。

例如，如果，：工作表Sheet1是积极的，这将引发错误：

Worksheets("Sheet2").Range(Cells(1,1),Cells(1,2))._ANY_METHOD_

你当然有，作为一个潜在的错误：

Worksheets("Master Scores").Range(Cells.Find(iCell.Value).EntireColumn

这将产生一个错误，这是因为其他的片材被激活：

Worksheets("EXPORT").Activate

有解决此两种方法：我不建议的基本途径是不厌其烦地跟踪哪些片是“活动”，完全符合一切。 但是，这是一个痛苦的对接，并就马虎，字迹模糊的代码。 也可以看看：

如何避免在Excel中VBA宏使用选择

要解决这种情况的另一种方式是适当资格的范围。 对于简单的情况下，你可以使用一个With块：

With Worksheets("Master Scores")
    iCell.Formula = .Range(.Cells.Find(iCell.Value)...

End With

但是，因为你至少有两个不同的表引用，这将是不可能的。 你必须定义一个有资格的某一范围的变量，并使用这些替代，你试图做复杂的级联。

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

然后，你可以这样做：

iCell.Formula = Application.Index(INDEX_ARRAY, INDEX_ROW, INDEX_COLUMN)

还要注意

您正在使用.Find方法和.Match公式字符串中的功能。 如果无论是在一个错误的结果，整个语句会报错。 我建议你调试的，可以考虑分别评估每件，检查错误，然后生成字符串的.Formula只有当你已经确保它不会出错。

文章来源: VBA Index Match Error