Hi, guys. I'm new to VBA and currently working on a project. I've run into a problem; so basically I have a "data" sheet stating what COMPANIES are present in COUNTRIES. I load the company names onto a combobox named companyComboBox.
The problem comes in where I'm trying to code this process:
I need to load UNIQUE country values into another combobox where the user-selected COMPANY (companyComboBox.Value) is in the COUNTRY
(data sheet)
COUNTRY COLUMN COMPANY
Angola Company A
Pakistan Company A
China Northern Lights
I've been thinking doing a With statement where I compare the row number of the COMPANY range with the COUNTRY range however is having multiple items in a WITH statement possible?
Thanks in advance, guys.
=========================================================================================================================
Here's the code;
lastrowFirm As Long, lastrowCountry As Long
lastrowFirm = Cells(7, Columns.Count).End(xlDown).Row
Dim v, e, c, s
With Sheets("data").Range("G2:G" & lastrowFirm)
v = .Value
End With
With CreateObject("Scripting.Dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) And e <> "" Then .Add e, Nothing
' Basically whats happening here is I'm adding all unique company names to my combobox
Next
If .Count Then Me.companyComboBox.List = Application.Transpose(.keys)
End With
lastrowCountry = Cells(1, Columns.Count).End(xlDown).Row
'This is where I'm loading the COUNTRY column
With Sheets("data").Range("A2:A" & lastrowCountry)
s = value
End With
For Each c in s
If Not .exists(c) And c <> "" And '(Condition to check if companyComboBox.Value is working in that COUNTRY)
Bookmarks