vba Excel：检查列中是否存在单元格字符串值，并获取对该字符串的所有单元格引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28400410/
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: Check if cell string value exists in column, and get all cell references to that string
提问by user2565123
I suspect this may be a job for VBA, which is beyond my abilities. But here's the scenario:
我怀疑这可能是 VBA 的工作，这超出了我的能力。但这是场景：
Column A in Sheet 1 (CAS1) contains x rows of text values
工作表 1 (CAS1) 中的 A 列包含 x 行文本值
Column A in Sheet 2 (CAS2) contains x rows of text values
Sheet 2 (CAS2) 中的 A 列包含 x 行文本值
Part A - For each row value in CAS1, I need to know if the string is contained in any of the cells in CAS2. Not exact match, the string can be only part of the searched cells.
A 部分 - 对于 CAS1 中的每一行值，我需要知道字符串是否包含在 CAS2 中的任何单元格中。不完全匹配，字符串只能是搜索单元格的一部分。
Part B - I need to know the cell value of each cell in CAS2 that contains the CAS1 value (if they do exist, they can be listed in the cells adjacent to the cell being searched in CAS1).
B 部分 - 我需要知道包含 CAS1 值的 CAS2 中每个单元格的单元格值（如果它们确实存在，它们可以列在与 CAS1 中正在搜索的单元格相邻的单元格中）。
I've tried the following to attempt Part A, all to no avail:
我尝试了以下尝试 A 部分，但都无济于事：
vlookup(A1,sheet2!A:A,1,false)
NOT(ISNA(MATCH(A1,sheet2!A:A,0)))
ISNUMBER(MATCH(A1,sheet2!A:A,0))
COUNTIF(sheet2!A:A,A1)>0
IF(ISERROR(MATCH(A1,sheet2!A:A, 0)), "No Match", "Match")
I know some of the cell values in CAS2 contain the cell values in CAS1, so I don't know why they return false or No Match. I suspect it may be down to the nature of the text content. So here's some sample data:
我知道 CAS2 中的某些单元格值包含 CAS1 中的单元格值，所以我不知道它们为什么返回 false 或 No Match。我怀疑这可能取决于文本内容的性质。所以这里有一些示例数据：
CAS1
CAS1
LQ056
RV007H
RV008
RV009H
TSN304
TSN305
CAS2
CAS2
RV009-satin-nickel-CO.jpg
STR314.jpg
STR315.jpg
HCY001.jpg
RV008-oval-rad-CO.jpg
HCY001-BRAC006.jpg
Any help would be appreciated.
任何帮助，将不胜感激。
采纳答案by Matteo NNZ
This problem can be faced through VBA (at least, I imagine the VBA solution much more easily than the possible Excel one). You need a macro that, for each row in CAS1, search the content in each row of CAS2 and returns you the address.
这个问题可以通过 VBA 来解决（至少，我认为 VBA 解决方案比可能的 Excel 解决方案更容易）。您需要一个宏，用于针对 CAS1 中的每一行，搜索 CAS2 中每一行中的内容并返回地址。
For Each cell In Sheets("CAS1").Range("A1:A" & Sheets("CAS1").Range("A1").End(xlDown).Row) '<-- check each cell of the range A1:A? of sheet CAS1 (adapt "A" and "1" if they're different)
recFound = 0 '<-- count how many findings there are
For Each cell2 In Sheets("CAS2").Range("A1:A" & Sheets("CAS2").Range("A1").End(xlDown).Row) '<-- check in each cell of the range A1:A? of sheet CAS2 (adapt "A" and "1" if they're different)
If InStr(cell2.Value, cell.Value) <> 0 Then '<-- if the value in cell is contained in the value in cell2..
recFound = recFound + 1 '<-- account the new finding
cell.Offset(0, recFound) = Split(cell2.Address, "$")(1) & Split(cell2.Address, "$")(2) '<--write the address on the right of the currently searched cell
End If
Next cell2
Next cell
All the above should be enclosed in a macro, e.g. Sub makeMySearch(), that should be run to get the results. As commented in my code, I'm assuming that data are in A1:A?of both sheets; but they of course might be, for example, in B5:B?of the sheet 1 and in C7:C?of the sheet 2. You need clearly to adapt the code to your current data.
以上所有内容都应包含在一个宏中，例如Sub makeMySearch()，应运行该宏以获得结果。正如我的代码中所评论的，我假设数据都在A1:A?两张表中；但它们当然可能是，例如，在B5:B?工作表 1 和工作C7:C?表 2 中。您需要清楚地使代码适应您当前的数据。
回答by Ron Rosenfeld
There's no need for VBA. Some simple array-formulas can do the job.
不需要VBA。一些简单的数组公式可以完成这项工作。
To see if the entry in CAS1 is present in CAS2:
要查看 CAS1 中的条目是否存在于 CAS2 中：
=OR(ISNUMBER(SEARCH(A2,CAS2_)))
will return TRUE or FALSE. BUTthis formula has to be entered by holding down CTRL-SHIFTwhile hitting ENTERIf you do this correctly, Excel will place braces {...}around the formula that you can see in the formula bar.
将返回 TRUE 或 FALSE。 但是这个公式必须通过在按下CTRL-SHIFT 的同时按下ENTER 来输入如果你正确地做到了这一点，Excel 会在你可以在公式栏中看到的公式周围放置大括号{...}。
The SEARCH function returns an array of results, which will be either the #VALUE! error, or a number.
SEARCH 函数返回一个结果数组，它可以是 #VALUE! 错误，或一个数字。
In order to return the address, the following array-formulacan be entered adjacent to a cell in CAS1:
为了返回地址，可以在 CAS1 中的单元格附近输入以下数组公式：
=IFERROR(ADDRESS(LARGE(ISNUMBER(SEARCH($A2,CAS2_))*ROW(CAS2_),COLUMNS($A:A)),1),"")
Fill right for the maximum number of addresses possible, then select the group and fill down.
为可能的最大地址数向右填写，然后选择组并向下填写。
In this case, the array being returned is a string of either 0's, or 1 * the row number (i.e. the row number). I assumend the data in CAS2 was in column A, but you can change the column number if needed (or even compute it if necessary, by replacing the 1in the ADDRESSfunction with COLUMN(CAS2_))
在这种情况下，返回的数组是一个由 0 或 1 * 行号（即行号）组成的字符串。我假设 CAS2 中的数据在 A 列中，但您可以根据需要更改列号（或者甚至在必要时计算它1，将ADDRESS函数中的替换为COLUMN(CAS2_)）
CAS1_ and CAS2_ are either named ranges, or absolute range references to the two text groups.
CAS1_ 和 CAS2_ 要么是命名范围，要么是对两个文本组的绝对范围引用。
