匹配项VBA的向量 [英] Vector of matches VBA

问题描述

我目前正在寻找以下问题.我想找到特定匹配项的每个位置，并将它们存储到称为pos的向量中.在我的示例中，我想知道出现"SG"的范围(E1:E500)中的每一行.然后，我想遍历此向量.

I am currently looking for the following issue. I would like to find every position of a particular matches and store them into a vector called pos. In my example I would like to know every row in the range(E1:E500) where "SG" appears. Then I would like to loop through this vector.

我尝试了以下代码，但似乎无法正常工作.谁能帮我吗?

I have tried the following code but it seems not to work. Can anyone help me?

Sub test()
    Set rangenew = Range("E1:E500")

    pos = Application.Match("SG", rangenew, False)

End Sub

结果应该类似于

pos = (1,6,8,10)

然后，我想遍历此向量以测试条件.

Then I would like to loop through this vector to test conditions.

感谢您的大力帮助.

推荐答案

正如@GSerg在评论中所述，没有内置函数可用于返回匹配的行号.像下面这样的事情应该可以满足您的要求.

As @GSerg explained in the comments, there is no built-in function for returning matched row numbers. Something like the below should do what you're after.

Public Sub getRows()

    Dim wb As Workbook, ws As Worksheet
    Dim checkData() As Variant, pos() As Long
    Dim i As Long
    Dim matchCount As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)

    checkData = ws.Range("E1:E500")

    For i = LBound(checkData, 1) To UBound(checkData, 1)

        If checkData(i, 1) = "SG" Then

            matchCount = matchCount + 1
            ReDim Preserve pos(1 To matchCount)
            pos(matchCount) = i

        End If

    Next i

End Sub

这篇关于匹配项VBA的向量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！