Filling an array based on combobox value

z0de

Board Regular
Joined
Oct 22, 2010
Messages
75
I have a user form with combo boxes and text boxes, Depending on the values chosen I want to call some other values from a worksheet and fill a row in an array for manipulation and then outputting to a worksheet.

For example:

  • The user selects Apple from the first combo box
  • User types 50 in the first text box
  • The user selects Banana from the second combo box
  • The user types 30 in the second combo box
  • code checks the named range for Apple
  • first row of array is filled with the values next to the word apple.
  • The values are divided by 100(a constant) and then multiplied by 50(the user variable)
  • code checks the named range for Banana
  • second row of the array is filled with the values next to the word Banana.
  • The values are divided by 100(a constant) and then multiplied by 30(the second user variable)
  • The values in the 'columns' are added and put in to a worksheet.

I uploaded some sample data http://www.sendspace.com/file/femch6http://www.sendspace.com/file/etilg6
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try this:- Your Userform Should Have Combobox1, Combobox2, TextBox1, TextBox2 and CommandButton1.
The data is based on sheet example below and the Selection of "Apple & Banana".
Code:
[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A)     [/B][/COLOR][COLOR=royalblue][B]Col(B)  [/B][/COLOR] [COLOR=royalblue][B]Col(C) [/B][/COLOR][COLOR=royalblue][B]Col(D) [/B][/COLOR][COLOR=royalblue][B]Col(E) [/B][/COLOR][COLOR=royalblue][B]Col(F) [/B][/COLOR][COLOR=royalblue][B]Col(G) [/B][/COLOR][COLOR=royalblue][B]Col(H) [/B][/COLOR][COLOR=royalblue][B]Col(I) [/B][/COLOR][COLOR=royalblue][B]Col(J) [/B][/COLOR][COLOR=royalblue][B]Col(K) [/B][/COLOR]
1.      Apple       10       11      12      13      14      15      16      17      18      19     
2.      Orange                                                                                      
3.      Banana      1        2       3       4       5       6       7       8       9       10     
4.      Pear                                                                                        
5.      Peach                                                                                       
6.      Plum                                                                                        
7.      Gratefruit                                                                                  
8.                                                                                                  
9.                  Results                                                                         
10.                 5.3      6.1     6.9     7.7     8.5     9.3     10.1    10.9    11.7    12.5
Regards Mick
Code:
Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
'[COLOR=green][B]Set Two array One for Data Input One for Results Array[/B][/COLOR]
'[COLOR=green][B]Code assumes there are only 10 Data Columns (11 in all) to go in Array[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Ray(1 To 10, 1 To 2)
[COLOR=navy]Dim[/COLOR] nRay(1 To 10)
[COLOR=navy]Dim[/COLOR] oSum [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
'[COLOR=green][B]Fruit Types obtained from Sheet Range "MyRng" (Range("A1:A20"))[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("MyRng")
[COLOR=navy]For[/COLOR] Col = 1 To 2
  '[COLOR=green][B]The Two rows (Apple & Banana) Required from Range Offset are Sent[/B][/COLOR]
  '[COLOR=green][B] to Column 1 & 2 of Array Ray[/B][/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Dn = Me.Controls("Combobox" & Col).Object.value [COLOR=navy]Then[/COLOR]
             [COLOR=navy]For[/COLOR] Ac = 1 To 10
               '[COLOR=green][B] Place "MyRng" offset Values in array "Ray"[/B][/COLOR]
                Ray(Ac, Col) = Dn.Offset(, Ac) * Me.Controls("TextBox" & Col).Object.value / 100
            [COLOR=navy]Next[/COLOR] Ac
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Next[/COLOR] Col
'[COLOR=green][B]Loop through both columns of array adding and placing in array "nRay"[/B][/COLOR]
[COLOR=navy]For[/COLOR] oSum = 1 To 10
    nRay(oSum) = Ray(oSum, 1) + Ray(oSum, 2)
[COLOR=navy]Next[/COLOR] oSum
'[COLOR=green][B]Place final Data on Sheet[/B][/COLOR]
Range("B10").Resize(, 10) = nRay
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top