VBA - 匹配范围以查看单元格值是否在数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26166323/
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
VBA - Match range to see if cell value is in array
提问by JV33
ISOmatch contains all the values that are in the arrays sISO and sISOnew. I am looking to copy values from approximately 200 different workbooks int one file. ISOmnatch is a column of all the ISO codes in the arrays.
ISOmatch 包含数组 sISO 和 sISOnew 中的所有值。我希望将大约 200 个不同工作簿中的值复制到一个文件中。ISOmnatch 是数组中所有 ISO 代码的列。
I am trying to have it cycle through each book, copy a value, find the ISO match and paste that value, then move to the next book, find the iso match, etc.
我试图让它在每本书中循环，复制一个值，找到 ISO 匹配并粘贴该值，然后移动到下一本书，找到 ISO 匹配等。
Here is the code I have worked up so far. The main issue I am hacing is how to detect if there is a match or not such as "If cell.value = sISO() or sISOnew() Then". I am not too sure how to implement the embedded excel match function or if that would even be easier.
这是我迄今为止编写的代码。我遇到的主要问题是如何检测是否存在匹配项，例如“If cell.value = sISO() or sISOnew() Then”。我不太确定如何实现嵌入式 excel 匹配功能，或者这是否更容易。
Sub NTSDM_Econ()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim isoWF, isoNWF As String
Dim sISO As Variant
Dim sISOnew As Variant
isoWF = "J:\Washington\Groups\CIG\GRS\Workfiles\"
isoNWF = "J:\Washington\Groups\CIG\GRS\Workfiles\New Countries\"
sISO = Array( _
"AFG", "AGO", "ALB", "ARE", "ARG", "ARM", "AUS", "AUT", "AZE", "BDI", "BEL", "BFA", "BGD", _
"BGR", "BHR", "BIH", "BLR", "BOL", "BRA", "BTN", "BWA", "CAN", "CHE", "CHL", "CHN", _
"CIV", "CMR", "COG", "COL", "CRI", "CUB", "CYP", "CZE", "DEU", "DNK", "DOM", "DRC", "DZA", _
"ECU", "EGY", "ESP", "EST", "FIN", "FRA", "GAB", "GBR", "GEO", "GHA", "GIN", "GNQ", "GRC", _
"GTM", "HKG", "HND", "HRV", "HUN", "IDN", "IND", "IRL", "IRN", "IRQ", "ISL", "ISR", "ITA", _
"JOR", "JPN", "KAZ", "KEN", "KGZ", "KHM", "KOR", "KOS", "KWT", "LAO", "LBN", "LBR", "LBY", _
"LKA", "LSO", "LTU", "LVA", "MAR", "MDA", "MDG", "MEX", "MKD", "MLI", "MMR", "MNE", "MNG", _
"MOZ", "MRT", "MUS", "MWI", "MYS", "NAM", "NCL", "NER", "NGA", "NIC", "NLD", "NOR", "NPL", _
"NZL", "OMN", "PAK", "PAN", "PER", "PHL", "PNG", "POL", "PRI", "PRT", "PRY", "QAT", "ROM", _
"RUS", "SAU", "SDN", "SEN", "SGP", "SLE", "SLV", "SRB", "SSD", "SVK", "SVN", _
"SWE", "SWZ", "SYR", "TGO", "THA", "TJK", "TKM", "TLS", "TTO", "TUN", "TUR", "TWN", "TZA", _
"UGA", "UKR", "URY", "USA", "UZB", "VEN", "VNM", "YEM", "ZAF", "ZMB", "ZWE")
sISOnew = Array( _
"ABW", "AIA", "AND", "ASM", "ATG", "BEN", "BHS", "BLZ", "BMU", "BRB", "BRN", "CAF", "COM", _
"CPV", "CUW", "CYM", "DJI", "DMA", "ERI", "ETH", "FJI", "FSM", "GMB", "GNB", "GRD", "GUF", _
"GUM", "GUY", "HTI", "JAM", "KIR", "KNA", "LCA", "LIE", "LUX", "MAC", "MCO", "MDV", "MHL", _
"MLT", "MTQ", "NRU", "PLW", "PRK", "PSE", "REU", "RWA", "SLB", "SMR", "SOM", "STP", "SUR", _
"SXM", "SYC", "SYC", "TCD", "TON", "TUV", "VCT", "VIR", "VUT", "WSM")
' smaller array used to test
sISO = Array("AFG", "AGO")
For Each ctry In sISO
For Each Cell In Range("B4:B214")
If UBound(Filter(sISO, Cell.Value)) > -1 Then
'' do some stuff
ActiveWorkbook.Close
End If
Next
Next ctry
End Sub
采纳答案by JNevill
You can test if an item is in an array with Filter(). Instead of If cell.value = sISO() or sISONew()something like if UBound(Filter(sISO, cell.value)) > -1 or UBound(Filter(sISONew, cell.value)) > -1 then ...should work.
您可以使用 Filter() 测试项目是否在数组中。而不是If cell.value = sISO() or sISONew()像 if UBound(Filter(sISO, cell.value)) > -1 or UBound(Filter(sISONew, cell.value)) > -1 then ...应该工作。
回答by Nybbe
You can use the function InStr() to see if the values match, but you then need to have all your values in a string. Here is the part of the code that could do that. Just make sure you fill the array before you enter the loops
您可以使用函数 InStr() 来查看值是否匹配，但是您需要将所有值都放在一个字符串中。这是可以做到这一点的代码部分。只需确保在进入循环之前填充数组
Dim ISOlist As String
Dim i As Long
Dim myVal As String
'Fill the two arrays into one long string with comma-separated values.
'The string need to make sure there is a comma both before and after each value
For i = LBound(sISO) To UBound(sISO)
ISOlist = ISOlist & "," & sISO(i)
Next
For i = LBound(sISOnew) To UBound(sISOnew)
ISOlist = ISOlist & "," & sISOnew(i)
Next
ISOlist = ISOlist & "," 'the ending comma is important
'Get the value that I should test, adding a comma before and after
myVal = "," & cell.value & ","
'If cell.Value = sISO() Or sISOnew() Then
If InStr(myVal, ISOlist) > 0 Then
