Excel 2010索引匹配VBA
尝试了一切，但似乎无法得到这个。尝试使用另一个工作表中索引匹配中的正确站点标记替换一个工作表的 B 行（站点标记）中的值。
工作表（Site_Visit） 网站标签 AL27 AS26 GBEM4 ...
工作表（站点） 站点标签 项目名称 203AL27 AL27 203AS26 AS26 201GBEM4 GBEM4 ……
我需要将 Sheets("Site_Visit") 中的值 SiteTag 替换为 Sheets("Sites") 中的相应 SiteTag。
现在，我只是尝试获取代码，将正确的索引值放入一个变量中，我将在该变量中将其作为每个单元格的值，并在循环中运行它。但我一生都无法让它获得价值。这就是我对变量所做的尝试（所有内容都已声明）。
ST_Cells2 = Application.WorksheetFunction.Index("Sites!A2:A34", Application.WorksheetFunction.Match("Site_Visit!B2", "Sites!B2:B34", 0), 0)
其中“Sites!A2:A34”是适当替换值的范围 “Sites_Visit!B2”是查找值 “Sites!B2:B34”是查找范围，
我收到运行时错误'1004'无法获取 WroksheetFunction 类的 Match 属性。有人有什么想法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Index 和 Match 函数需要范围，但您向它们发送字符串。将字符串转换为范围的最简单方法是使用 Excel 的 Range 函数：
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:
我遇到了同样的错误，但是当我更改为“Application”而不是 WorksheetFunction 时，它运行正常：
Cells(12, 12).Value = Application.Index("Sheet1!B1:9", 2)不知怎的，直接从应用程序运行该功能是有效的...
/K
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