vba 如果单元格与范围内的任何其他单元格匹配，则将内容复制到“结果”表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9470554/
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
If cell matches any others in range copy content to "results" sheet
提问by sam
I want to check if the text in A3 matches the text in any other cells in range A5:A50.
我想检查 A3 中的文本是否与 A5:A50 范围内的任何其他单元格中的文本匹配。
If it does I want to copy the contents of the Y cell (with the same row number as the a cell that matched A3) into the next free cell in the A column in a sheet called results. It needs to also keep the cell colour when it copies the Y cell.
如果确实如此，我想将 Y 单元格的内容（与匹配 A3 的单元格具有相同的行号）复制到名为 results 的工作表中 A 列中的下一个空闲单元格。它在复制 Y 单元格时还需要保持单元格颜色。
All I could get was putting this in the destination cell
我所能得到的就是把它放在目标单元格中
= VLOOKUP(Sheet1!A3, Sheet1$A:$Y50, 25, TRUE)
That doesn't work as I need to run it over multiple sheets all with different names.
这不起作用，因为我需要在多个名称不同的工作表上运行它。
采纳答案by Tim Williams
with activesheet
for each c in .range("A5:A50").cells
if c.value = .range("A3").value then
c.offset(0,24).copy _
worksheets("results").cells(rows.count,1).end(xlup).offset(1,0)
end if
next c
end with
回答by Jerry Beaucaire
You can employ a dynamic 3d-Vlookup technique by listing the sheetnames to search in a list somewhere, naming that range of cells MySheetsand then use a construct like this:
您可以使用动态 3d-Vlookup 技术，通过列出工作表名称在列表中的某处进行搜索，将该范围的单元格命名为MySheets，然后使用如下结构：
=VLOOKUP(A3, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!A1:A50"), A3), 0)) & "'!A:B"), 2, 0)
There's a sample workbook on my sitedemonstrating this.
