Excel 2010 索引匹配 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7576947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2010 index match VBA
提问by Jason Barnes
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.
尝试了一切，似乎无法得到这个。尝试将一个工作表的 B 行 (SiteTag) 中的值替换为另一个工作表中的索引匹配中的正确站点标签。
Worksheet(Site_Visit) SiteTagAL27 AS26 GBEM4 ...
Worksheet(Sites) SiteTagProject Name203AL27 AL27 203AS26 AS26 201GBEM4 GBEM4 ... ...
工作表(Site_Visit) SiteTagAL27 AS26 GBEM4 ...
工作表（站点） SiteTag项目名称203AL27 AL27 203AS26 AS26 201GBEM4 GBEM4 ... ...
I need to replace the values SiteTag in Sheets("Site_Visit") with the appropriate SiteTag from Sheets("Sites").
我需要用 Sheets("Sites") 中的适当 SiteTag 替换 Sheets("Site_Visit") 中的值 SiteTag。
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
其中“Sites!A2:A34”是适当替换值的范围“Sites_Visit!B2”是查找值“Sites!B2:B34”是查找范围
I'm getting a Run Time error '1004' Unable to get the Match property of the WroksheetFunction class. Anyone have any ideas?
我收到运行时错误'1004' Unable to get the Match property of the WroksheetFunction class。谁有想法？
回答by Idan Arye
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:
Index 和 Match 函数需要范围，但您正在向它们发送字符串。将字符串转换为 Ranges 的最简单方法是使用 Excel 的 Range 函数：
st_cells2 = Application.WorksheetFunction.Index(Range("Sites!A2:A34"), Application.WorksheetFunction.Match(Range("Site_Visit!B2"), Range("Sites!B2:B34"), 0), 0)
回答by Klas
I had the same error, but it run ok when I changed to "Application" indstead of WorksheetFunction:
我有同样的错误，但是当我改为“应用程序”而不是 WorksheetFunction 时它运行正常：
Cells(12, 12).Value = Application.Index("Sheet1!B1:9", 2)
Cells(12, 12).Value = Application.Index("Sheet1!B1:9", 2)
Somehow running the Function from Application directly worked... /K
不知何故从应用程序直接运行函数工作... /K
