Sheet1
Error during initialize:
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
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