获取VBA中数组的第N个索引 [英] Get the Nth index of an array in VBA

问题描述

我在VBA中是菜鸟，无法找到在给定索引处获取数组元素的方法……不过，这对您来说可能很容易.

I am a noob in VBA and can't find a way to get the element of an array at a given index... It might be easy for you, though.

我有一个带有两列的"excel"文件，电子邮件"和类别"，我想过滤出给定类别的所有电子邮件.

I have an excel file with 2 columns, "Emails" and "Categories", and I want to filter out all emails for a given category.

到目前为止，我得到了以下代码:

I ended up so far with the following code:

Sub filterEmails()

    Dim tbl As ListObject
    Dim emails As Variant
    Dim email As String
    Dim categories As Variant
    Dim category As String
    Dim i As Integer

    Set tbl = ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1")
    emails = tbl.ListColumns("EMAILS").DataBodyRange.Value
    categories = tbl.ListColumns("SERVICES").DataBodyRange.Value

    i = 1
    For Each email In emails

        category = ???

        If category = "some service" Then
            MsgBox email
        End If

        i = i + 1
    Next email

End Sub

我尝试了多种方法从Categories数组中获取第i个项目，例如 categories(i)，但没有成功.可能是因为我无法用正确的类型初始化变量.

I tried many ways to get the ith item from the categories array, like categories(i) but didn't succeed. It might be because I wasn't able to initialize variables with the right type.

推荐答案

我会这样做:

Sub filterEmails()

    Dim tbl As ListObject
    Dim emails As Variant
    Dim email As String
    Dim categories As Variant
    Dim category As String
    Dim i As Long '<< always best to prefer Long over Integer

    Set tbl = ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1")

    'Both "emails" and "categories" will be 2-D arrays
    emails = tbl.ListColumns("EMAILS").DataBodyRange.Value
    categories = tbl.ListColumns("SERVICES").DataBodyRange.Value


    For i = lbound(emails,1) to ubound(emails, 1) 

        category = categories(i, 1)

        If category = "some service" Then
            MsgBox email
        End If
    Next i

End Sub

这篇关于获取VBA中数组的第N个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！