数组中的通配符搜索 [英] Wildcard search in array

问题描述

我有一个数组，其中包含一些常用术语，用于表示该字段正在等待一个PO。然后，我有一个循环，通过大量数据删除任何行，其中单元格中的值与数组中的任何术语不匹配。为了做到这一点，我使用一个函数（我在网上找到）来测试数组是否存在于数组中。

I have an array containing a bunch of common terms used to denote that the field is awaiting a PO. Then I have a loop to go backwards through a large column of data deleting any rows where the value in the cell doesn't match any of the terms in the array. To do this I use a function (which I found online) to test if the value exists in the array.

到目前为止这么好，只要在单元格精确匹配数组中的值。如果一个单元格对一个常用术语有一个微小的变化（即TBC - 将会跟随或者甚至只是TBC，而不是TBC），那么它就是错误的。

So far so good, as long as the value in the cell matches a value in the array exactly. Where it goes wrong is if a cell contains a slight variation on one of the common terms (ie. "TBC - will follow later" or even just "TBC " instead of "TBC" exactly)

我需要一种方法来获取单元格中的值，并对数组中的值进行通配符搜索。我不会粘贴我的所有代码（这是一个中期开发的混乱现在），但如果我们可以下面的工作，我可以应用它。

I need a way to take the value in the cell and do a wildcard search against the values in the array. I won't paste all my code (it's a mid-development mess right now), but if we can get this below to work I can apply it.

Sub TestFilterArray()
MyArray = Array("tbc", "awaiting po", "po to follow")
If IsInArray("tbc xyz", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

这个tb xyz不在数组中，但是如果有意义的话，我希望它返回是！，tbc *在那里。

This currently returns "No!..." for "tbc xyz" not being in the array but I'd like it to return "Yes!..." for "tbc*" being there if that makes sense.

任何帮助感谢。

推荐答案

Function IsInArray2(StringToBeFound As String, MyArray As Variant) As Boolean
IsInArray2 = False
For i = LBound(MyArray) To UBound(MyArray)
    If "*" & MyArray(i) & "*" Like StringToBeFound Then IsInArray2 = True 'will match MyArray to any substring of StringToBeFound
Next
End Function

考虑到运行时的考虑，它成为

With runtime considerations in mind it becomes

Function IsInArray2(StringToBeFound As String, MyArray As Variant) As Boolean
IsInArray2 = False
For i = LBound(MyArray) To UBound(MyArray)
    If "*" & MyArray(i) & "*" Like StringToBeFound Then
        IsInArray2 = True 'will match MyArray to any substring of StringToBeFound
        Exit Function
    End If
Next
End Function

谢谢你的发言。回头看，对于像语句一样，对不起，对不起。让我用一个案例和冗余匹配器来弥补它，并进行测试来显示它的重要性。

Thank you for the remark. Looking back, yep the Like statement works the other way around, sorry. Let me make up for it with a case and redundancy matcher and a test to show its importance.

Function IsInArray2(stringToBeFound As String, MyArray As Variant) As Boolean
IsInArray2 = False
For i = LBound(MyArray) To UBound(MyArray)
    If LCase(stringToBeFound) Like LCase("*" & Replace(MyArray(i), " ", "*") & "*") Then
        IsInArray2 = True 'will match MyArray to any substring of StringToBeFound
        Exit Function
    End If
Next
End Function

Sub TestFilterArray()
MyArray = Array("coca cola gmbh", "awaiting po", "po to follow")
If IsInArray2("Coca Cola Deutschland GmbH", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

这篇关于数组中的通配符搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！