VBA阵列-检查严格(非近似)匹配 [英] VBA Arrays - Check strict (not approximative) match

问题描述

If UBound(Filter(myArray, Sheets(i).Cells(1, j).Value, True)) = -1 Then
 'take action
End if

我使用此语法将在Cells(1，j)中找到的元素(例如"ally")与数组的所有元素(例如"mally"，"kate"，"becks")进行比较，并采用未找到完全匹配项时采取的措施. 麻烦的是，根据这一行代码，似乎盟友"被认为与盟友"匹配(可能是因为盟友"是盟友"的子字符串)，而我希望盟友"被识别为与盟友"不同.

I used this syntax to compare an element found in Cells(1, j) (e.g. "ally") to all the elements of an array (e.g. "mally", "kate", "becks"), and to take action when no exact match is found. Trouble is, based on this line of code it seems "ally" is considered as matching "mally" (probably because "ally" is a substring from "mally"), whereas I want "ally" to be recognised as distinct from "mally".

对实现此语法的语法有帮助吗?谢谢！

Any help with the syntax as to achieve this? Thank you!

推荐答案

过滤器将返回部分匹配的所有项目. Microsoft建议的解决方法是，然后在过滤后的数组中搜索完全匹配.

Filter will return any items that partially match. The work around suggested by Microsoft is to then search the filtered array for exact matches.

Function FilterExactMatch(astrItems() As String, _
                          strSearch As String) As String()

   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long

   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)

   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)

   ' Resize temporary array to be same size.
   ReDim astrTemp(lngLower To lngUpper)

   ' Loop through each element in filtered array.
   For lngIndex = lngLower To lngUpper
      ' Check that element matches search string exactly.
      If astrFilter(lngIndex) = strSearch Then
         ' Store elements that match exactly in another array.
         astrTemp(lngCount) = strSearch
         lngCount = lngCount + 1
      End If
   Next lngIndex

   ' Resize array containing exact matches.
   ReDim Preserve astrTemp(lngLower To lngCount - 1)

   ' Return array containing exact matches.
   FilterExactMatch = astrTemp
End Function

此代码摘自 http ://msdn.microsoft.com/zh-CN/library/office/aa164525%28v=office.10%29.aspx

这篇关于VBA阵列-检查严格(非近似)匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！