VBA Index Match Error

Question

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.

Without looking too much more into it, it's not Application.Index, it would be Application.WorksheetFunction... — John Bustos
Forgot to mention- I had it set to Application.WorksheetFunction... before and had just switched it back to try another way. I'll put it back though — Brumder
@JohnBustos - Dropping the WorksheetFunction part is allowed, and can be easier to code with, since you can check the return value using IsError() whereas hitting an error when using the WorksheetFunction version will raise a run-time error, which must then be handled. — Tim Williams

David Zemens David Zemens · Accepted Answer · 2014-09-19T22:10:41

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 Index Match Error

1 Answers