Excel 2010 index match VBA

Question

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?

Solution

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)