返回一个元素的索引数组中的Excel的VBA [英] Return Index of an Element in an Array Excel VBA
问题描述
我有一个数组prLst是整数列表。该整数没有排序,因为他们的阵列重新$ P $位置psents到S preadsheet特定列。我想知道我是怎么在数组中查找特定的整数,并返回其索引。
似乎没有要上展示我如何不将数组到工作表上的一个范围内的任何资源。这似乎有点复杂。使用VBA这是不可能的?
昏暗的POS机,编曲,VALARR =阵列(1,2,4,5)
VAL = 4POS = Application.Match(VAL,编曲,假)如果不是ISERROR(POS),然后
MSGBOX VAL&安培; 在位置与& POS
其他
MSGBOX VAL&安培; 未找到!
万一
更新使用匹配(具有的.index)是一个二维阵列的尺寸,以找到一个值,以显示:
暗淡ARR(1到10,1〜2)
昏暗的点¯x对于x = 1至10个
ARR(X,1)= X
ARR(X,2)= 11 - X
下一个xDebug.Print Application.Match(3,Application.Index(ARR,0,1),0)
Debug.Print Application.Match(3,Application.Index(ARR,0,2),0)
编辑:这是值得说明这里什么@ARich在评论中指出 - 在使用指数()
来切一个数组,如果你这样做是在可怕的表现一个循环
在测试(code以下)的Index()方法是近2000倍,比使用嵌套循环更慢。
子PerfTest() 常量VAL_TO_FIND的String =R1800:C8
暗淡(1至2000年,1到10)
昏暗ř长,C长,T 当r = 1到2000
对于C = 1〜10
一(R,C)=R&放大器; R&安培; :C&放大器; C
下一个C
接下来ř T =定时器
Debug.Print FindLoop(一,VAL_TO_FIND),定时器 - T的
'>> 0.00781秒 T =定时器
Debug.Print FindIndex(一,VAL_TO_FIND),定时器 - T的
'>> 14.18秒结束小组功能FindLoop(ARR,VAL)为布尔
昏暗ř长,C只要
当r = 1到UBound函数(ARR,1)
对于C = 1要UBound函数(ARR,2)
如果ARR(R,C)= VAL然后
FindLoop = TRUE
退出功能
万一
下一个C
接下来ř
结束功能功能FindIndex(ARR,VAL)
昏暗ř只要
当r = 1到UBound函数(ARR,1)
如果未ISERROR(Application.Match(VAL,Application.Index(ARR,R,0),0))。然后
FindIndex = TRUE
退出功能
万一
接下来ř
结束功能
I have an array prLst that is a list of integers. The integers are not sorted, because their position in the array represents a particular column on a spreadsheet. I want to know how I find a particular integer in the array, and return its index.
There does not seem to be any resource on showing me how without turning the array into a range on the worksheet. This seems a bit complicated. Is this just not possible with VBA?
Dim pos, arr, val
arr=Array(1,2,4,5)
val = 4
pos=Application.Match(val, arr, False)
if not iserror(pos) then
Msgbox val & " is at position " & pos
else
Msgbox val & " not found!"
end if
Updated to show using Match (with .Index) to find a value in a dimension of a two-dimensional array:
Dim arr(1 To 10, 1 To 2)
Dim x
For x = 1 To 10
arr(x, 1) = x
arr(x, 2) = 11 - x
Next x
Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)
EDIT: it's worth illustrating here what @ARich pointed out in the comments - that using Index()
to slice an array has horrible performance if you're doing it in a loop.
In testing (code below) the Index() approach is almost 2000-fold slower than using a nested loop.
Sub PerfTest()
Const VAL_TO_FIND As String = "R1800:C8"
Dim a(1 To 2000, 1 To 10)
Dim r As Long, c As Long, t
For r = 1 To 2000
For c = 1 To 10
a(r, c) = "R" & r & ":C" & c
Next c
Next r
t = Timer
Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
' >> 0.00781 sec
t = Timer
Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
' >> 14.18 sec
End Sub
Function FindLoop(arr, val) As Boolean
Dim r As Long, c As Long
For r = 1 To UBound(arr, 1)
For c = 1 To UBound(arr, 2)
If arr(r, c) = val Then
FindLoop = True
Exit Function
End If
Next c
Next r
End Function
Function FindIndex(arr, val)
Dim r As Long
For r = 1 To UBound(arr, 1)
If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
FindIndex = True
Exit Function
End If
Next r
End Function
这篇关于返回一个元素的索引数组中的Excel的VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!