Results 1 to 3 of 3

Thread: Solved: x dimensions array, combobox and loop...

  1. #1

    Solved: x dimensions array, combobox and loop...


    I have a form with a series of comboboxes. I would like to pass the values of the comboboxes to an array. I'll check if there are duplicates in the database. for the input which are not duplicates I'll store the values in the database.

    i would have 2 questions:

    - is it a good strategy?
    - how do i pass the values of the comboboxes into the array??

    i have a problem with the "i" in the loop...

    Dim aSecurities()
    ReDim aSecurities(1 To 6, 4)
    For i = 1 To 6
    aSecurities(i, 1) = Sectype & i & .value
    aSecurities(i, 2) = Zone & i &. value
    aSecurities(i, 3) = Valoren & i & .value
    aSecurities(i, 4) = Ticker & i & .value
    Next i


    i've been trying using double quotes and another few tricks but it didnt work...

    does anyone have an idea on how to deal with this please?

  2. #2
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    May 2004
    If I understand correctly,
    [VBA]aSecurities(i, 1) = controls("Sectype" & i) .value [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    thanks you!
    it works wonderfully well...

    I've done this (early exit if the valoren is empty)

    ReDim aSecurities(1 To 6, 4)
    For i = 1 To 6
    Control = Controls("Valoren" & i).Value
    If Control = "" Then Exit For
    aSecurities(i, 1) = Controls("Sectype" & i).Value
    aSecurities(i, 2) = Controls("Zone" & i).Value
    aSecurities(i, 3) = Controls("Valoren" & i).Value
    aSecurities(i, 4) = Controls("Ticker" & i).Value
    Next i

    just a quick question:

    I'd need to check for duplicates using the Valoren (which is stored in asecurities(i,3) ) as each security has a unique one. if this is a new Valoren I'll add the data from the form.

    what strategy would you suggest?? a vlookup? a match? something "lighter"?

Posting Permissions

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