在vba动态表中查找值 [英] Find value in a vba dynamic table
本文介绍了在vba动态表中查找值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Dim tableOfSizes()As String
sizeTable(0)= size1
sizeTable(1)= size2
sizeTable(2)= size3
'size2和size3是函数
的可选参数如果instr(tableOfSizes,Medium)< ;> 0然后
'如果在表中找到Medium,则动作
End If
但似乎instr不适用于表,或至少对于动态表。这是问题吗?
解决方案
对于 1D数组,您可以使用以下方法。 >
方式№1 过滤器功能
如果UBound(Filter(tableOfSizes,Medium))<> -1然后
'如果在表中找到Medium,则动作
End If
方式№2( for Excel-VBA
) Application.Match
如果不是IsError(Application.Match(Medium,tableOfSizes,0))然后
'表
如果
对于多维数组,您可以使用以下函数:
函数包含(arr,对于每个a在arr
如果a = elem然后
contains = True
退出函数
结束如果
下一个
结束函数
然后:
如果包含(tableOfSizes,Medium)Then
'Action如果在表中找到Medium
End If
I have a dynamic table of strings that I defined in vba (I precise that it is not an XL table) and I want to check if a specific value is present in this table. Here is a portion of my function:
Dim tableOfSizes() As String
sizeTable(0)=size1
sizeTable(1)=size2
sizeTable(2)=size3
'size2 and size3 are optional parameters of the function
If instr(tableOfSizes, "Medium") <> 0 Then
' Action if "Medium" is found in the table
End If
but it seems that instr do not work for tables, or at least, for dynamic tables. Is that the problem?
解决方案
For 1D-array you can use following approaches.
Way №1 Filter function
If UBound(Filter(tableOfSizes, "Medium")) <> -1 Then
' Action if "Medium" is found in the table
End If
Way №2 (for Excel-VBA
) Application.Match
If Not IsError(Application.Match("Medium", tableOfSizes, 0)) Then
' Action if "Medium" is found in the table
End If
For multi-dimmension arrays you can use following function:
Function contains(arr, elem) As Boolean
Dim a
contains = False
For Each a In arr
If a = elem Then
contains = True
Exit Function
End If
Next
End Function
and then:
If contains(tableOfSizes, "Medium") Then
' Action if "Medium" is found in the table
End If
这篇关于在vba动态表中查找值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文