Excel 2010 index match VBA

2
votes

Tried everything and can't seem to get this. Trying to replace values in Row B (SiteTag) of one worksheet with the proper sitetag from an index match in another worksheet.

Worksheet(Site_Visit) SiteTag AL27 AS26 GBEM4 ...

Worksheet(Sites) SiteTag Project Name 203AL27 AL27 203AS26 AS26 201GBEM4 GBEM4 ... ...

I need to replace the values SiteTag in Sheets("Site_Visit") with the appropriate SiteTag from Sheets("Sites").

For now I've simply tried to get the code to place the correct index value into a variable in which I'll place as the value for each cell, and run it in a loop. But for the life of me can't get it to get a value. This is what I've tried for the variable (everything has been declared). 

ST_Cells2 = Application.WorksheetFunction.Index("Sites!A2:A34", Application.WorksheetFunction.Match("Site_Visit!B2", "Sites!B2:B34", 0), 0)

Where "Sites!A2:A34" is the range for the appropriate replacement value "Sites_Visit!B2" is the lookup value "Sites!B2:B34" is the lookup range

I'm getting a Run Time error '1004' Unable to get the Match property of the WroksheetFunction class. Anyone have any ideas?

2
excelvbaindexingmatch

2 Answers

4
votes

The Index and Match functions are expecting Ranges, but you are sending them strings. The easiest way to turn strings into Ranges is to use Excel's Range function:

st_cells2 = Application.WorksheetFunction.Index(Range("Sites!A2:A34"), Application.WorksheetFunction.Match(Range("Site_Visit!B2"), Range("Sites!B2:B34"), 0), 0)
0
votes

I had the same error, but it run ok when I changed to "Application" indstead of WorksheetFunction:

Cells(12, 12).Value = Application.Index("Sheet1!B1:9", 2)

Somehow running the Function from Application directly worked... /K