excelvbavlookup

How to get the exact Team Name using Application.VLookup


Sheet1

Sheet1 img

Error during initialize:

error

What should be fixed in the code below to get the exact team name populated in the Me.cmbTeam.Value (combobox) if team member is matching in column 2.

Private Sub UserForm_Initialize()
    Me.cmbDev.Value = "Nory"

    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim i As Long
    Dim arr: arr = ws.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim teamName As Variant

    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = Empty
    Next
   ' ***
   
    If dict.exists(Me.cmbDev.Value) Then
        teamName = Application.VLookup(Me.cmbDev.Value, ws.Range("B1").CurrentRegion.Value, 1, False)
        Me.cmbTeam.Value = teamName 'should get a result of George
    Else
        Me.cmbDev.Value = ""
        Me.cmbTeam.Value = ""
    End If

End Sub

Solution

  • Private Sub UserForm_22Initialize33()
        Me.cmbDev.Value = "Nory"
    
        Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
        Dim i As Long
        Dim arr: arr = ws.Range("B1").CurrentRegion.Value
        Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
        Dim teamName As Variant
    
        For i = 2 To UBound(arr)
            dict(arr(i, 2)) = Empty
        Next
       ' ***
       
        If dict.exists(Me.cmbDev.Value) Then
            teamName = Application.VLookup(Me.cmbDev.Value, ws.Range("B1").CurrentRegion.Value, 1, False)
            Me.cmbTeam.Value = teamName 'should get a result of George
        Else
            Me.cmbDev.Value = ""
            Me.cmbTeam.Value = ""
        End If
    
    End Sub
    
    Private Sub UserForm_Initialize()
        Me.cmbDev.Value = "Masie"
    
        Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
        Dim i As Long
        Dim arr As Variant
        arr = ws.Range("B1").CurrentRegion.Value
    
        Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
        Dim teamName As Variant
    
        ' Populate dictionary and validate data
        For i = 1 To UBound(arr, 1) ' Ensure UBound uses the first dimension
            If Not dict.exists(arr(i, 2)) Then
                dict(arr(i, 2)) = arr(i, 1) ' Store team name against developer name
            End If
        Next i
    
        ' Debugging step: Verify contents of the dictionary
        Dim key As Variant
        For Each key In dict.keys
            Debug.Print key & " - " & dict(key)
        Next key
    
        ' Check if cmbDev.Value exists
        If dict.exists(Me.cmbDev.Value) Then
            teamName = dict(Me.cmbDev.Value) ' Retrieve team name directly from dictionary
            Me.cmbTeam.Value = teamName ' Should set cmbTeam.Value to "George"
        Else
            Me.cmbDev.Value = ""
            Me.cmbTeam.Value = ""
        End If
    End Sub