Get the Nth index of an array in VBA

Question

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.

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

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.

score 3 · Accepted Answer · answered May 06 '19 at 19:18

I would do it this way:

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

score 1 · Answer 2 · answered May 06 '19 at 19:09

Here's your code, changed it a little, It should work now:

    Option Explicit

    Sub filterEmails()

    Dim tbl As ListObject
    Dim emails As Variant
    Dim email As Variant
    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 = Application.Transpose(tbl.ListColumns("SERVICES").DataBodyRange.Value)

    i = 1
    For Each email In emails

        category = categories(i)

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

        i = i + 1
    Next email

End Sub

Comments:

categories(i)

That command wont work because categories is a 2 dimension array, I store it as 1 dimensional array using Application.transpose command.

Get the Nth index of an array in VBA

2 Answers2

Linked