我遇到的情况是,我有一组预定义的值/代码,这些值/代码组合在一起会发出一些消息。我也有一个传入文件,我必须对代码进行分析,然后将集合与预定义值进行比较。问题在于传入的文件与我的库不完全匹配:
在我的库中,我有4列的集合,每一行都有其独特的含义。我想将其理解为字符串,以便进一步进行计算。问题在于传入的文件不一致。它不遵循预定义文件中的确切顺序。
I need to compare them regardless of sequence. After being matched I want to grab corresponding Meaning from Library and proceed working with Case in my file.
Any ideas how to implement it in VBA?
Offhand, you could create a VBA function that would return a string representing the unique values.
If there is some character that will never be in the values, you could delimit the values with that character (such as _
):
Function GetUniqueValuesString(rng As Range) As String
Dim rngValues() As Variant
rngValues = rng.Value
' The following line requires a reference to Microsoft Scripting Runtime
' (via Tools -> References...)
Dim dict As New Scripting.Dictionary
'The function only parses the first row of the range
Dim i As Integer
For i = 1 To UBound(rngValues, 2)
dict(rngValues(1, i)) = 1 ' 1 here is a dummy value
Next
GetUniqueValuesString = Join(dict.Keys, "_")
End Function
(If every value is always the same number of characters, you could simply join them, without any delimiter.)
Using this function against a horizontal cell range:
=GetUniqeValuesString(A4:D4)
should return a string with only the unique values:
BGAA_TGHJ_WETY
If you apply this function to both the library rows and the file rows, you should be able to match on the function's returned value.
请注意,该函数具有一些可能需要解决的限制:
BGAA
,TGHJ
,WETY
将解析为一个不同的字符串比BGAA
,WETY
,TGHJ
。本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句