Results 1 to 4 of 4

VBA - Need help; multiple objects in a With statement

Threaded View

RedRepublic01 VBA - Need help; multiple... 04-05-2017, 08:54 PM
AlphaFrog Re: VBA - Need help; multiple... 04-05-2017, 11:05 PM
RedRepublic01 Re: VBA - Need help; multiple... 04-06-2017, 12:42 AM
AlphaFrog Re: VBA - Need help; multiple... 04-06-2017, 12:27 PM
  1. #1
    Registered User
    Join Date
    04-03-2017
    Location
    Manila, Philippines
    MS-Off Ver
    2016
    Posts
    10

    Question VBA - Need help; multiple objects in a With statement

    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)
    Last edited by RedRepublic01; 04-06-2017 at 12:42 AM. Reason: SOLVED

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Apply one code to multiple objects
    By ABSTRAKTUS in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-14-2016, 11:42 AM
  2. Delete multiple ink objects
    By gyster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2016, 04:26 PM
  3. [SOLVED] With Statement . Assignment to multiple objects
    By buhnen in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2016, 09:03 AM
  4. Multiple objects with "with" statement ?
    By Galven in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-10-2014, 05:02 PM
  5. Allocating expenses to multiple objects
    By easyice in forum Excel General
    Replies: 2
    Last Post: 06-04-2014, 02:17 PM
  6. How to hide a selection of objects but show objects automatically when opening wkbk
    By slowjo1414 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-05-2013, 05:24 PM
  7. Bin, Distribution multiple objects???
    By William Elerding in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-08-2006, 02:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1