Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Array questions
With a 2 column listbox, user is able to input values
to column 2 via a text box. How may I test that all entries in col 2 have been made before the OkButton_Click enables them to leave the form, please? I am struggling to understand the basics of arrays, and know that it is important to do so. Here is very inefficient working code which I'm sure is ideally suited to arrays: For Each Cell In .Range("G1:G250") For Each c In .Range("A1:A250") If Cell.Value = c.Value Then Cell.Offset(0, 1).Value = c.Offset(0, 1).Value End If Next Next I would be very grateful for help with this particular situation, and also any links to primers on arrays. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Array questions
Hi Stuart,
Just checking. Are you trying to make sure the user does not enter a value that already exists in the ListBox? Or are you trying to disable/enable the OK button depending on what the user has entered into the TextBox? Just trying to get a clearer picture of what you need. Thanks, James S -----Original Message----- With a 2 column listbox, user is able to input values to column 2 via a text box. How may I test that all entries in col 2 have been made before the OkButton_Click enables them to leave the form, please? I am struggling to understand the basics of arrays, and know that it is important to do so. Here is very inefficient working code which I'm sure is ideally suited to arrays: For Each Cell In .Range("G1:G250") For Each c In .Range("A1:A250") If Cell.Value = c.Value Then Cell.Offset(0, 1).Value = c.Offset(0, 1).Value End If Next Next I would be very grateful for help with this particular situation, and also any links to primers on arrays. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Array questions
Stuart
here is an example of getting the data into an array: Sub TestArray1() 'abcdefghijklmnopqrstuvwxyz in cells G1 to G26 Dim vArray() As Variant vArray = Application.Transpose(Range("G1:G26")) MsgBox vArray(1) & " " & vArray(26) End Sub Note that to get column data into an array it must be transposed. For your particular example, the following code seems to work and is quite quick: Sub TestArray2() Dim AvArray() As Variant Dim BvArray() As Variant Dim GvArray() As Variant Dim HvArray(1 To 250) As Variant Dim AIndex As Integer Dim GIndex As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual AvArray = Application.Transpose(Range("A1:A250")) BvArray = Application.Transpose(Range("B1:B250")) GvArray = Application.Transpose(Range("G1:G250")) For GIndex = 1 To 250 For AIndex = 1 To 250 If GvArray(GIndex) = AvArray(AIndex) Then HvArray(GIndex) = BvArray(AIndex) End If Next AIndex Next GIndex Range("H1:H250") = Application.Transpose(HvArray) Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub It is obviously constrained to 250 rows as that was what your example was using. Note that this is 62500 comparisons. Regards Trevor "Stuart" wrote in message ... With a 2 column listbox, user is able to input values to column 2 via a text box. How may I test that all entries in col 2 have been made before the OkButton_Click enables them to leave the form, please? I am struggling to understand the basics of arrays, and know that it is important to do so. Here is very inefficient working code which I'm sure is ideally suited to arrays: For Each Cell In .Range("G1:G250") For Each c In .Range("A1:A250") If Cell.Value = c.Value Then Cell.Offset(0, 1).Value = c.Offset(0, 1).Value End If Next Next I would be very grateful for help with this particular situation, and also any links to primers on arrays. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Array questions
Actually, that's a good point, and both are relevent!
I guess the ideal wold be: a: only load lb.col2 if the textbox value is valid AND is a unique value to lb.col2 b: then after each 'a:' test if lb.col2 is full (ie contains a value for each lb.col1 value) AND then enable the OkButton. It's referencing col2 in the listbox that's my first problem. Regards and thanks. "James S" wrote in message ... Hi Stuart, Just checking. Are you trying to make sure the user does not enter a value that already exists in the ListBox? Or are you trying to disable/enable the OK button depending on what the user has entered into the TextBox? Just trying to get a clearer picture of what you need. Thanks, James S -----Original Message----- With a 2 column listbox, user is able to input values to column 2 via a text box. How may I test that all entries in col 2 have been made before the OkButton_Click enables them to leave the form, please? I am struggling to understand the basics of arrays, and know that it is important to do so. Here is very inefficient working code which I'm sure is ideally suited to arrays: For Each Cell In .Range("G1:G250") For Each c In .Range("A1:A250") If Cell.Value = c.Value Then Cell.Offset(0, 1).Value = c.Offset(0, 1).Value End If Next Next I would be very grateful for help with this particular situation, and also any links to primers on arrays. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox and Array questions
Many thanks, both for the the explanation, and
the code for my example. Regards. "Trevor Shuttleworth" wrote in message ... Stuart here is an example of getting the data into an array: Sub TestArray1() 'abcdefghijklmnopqrstuvwxyz in cells G1 to G26 Dim vArray() As Variant vArray = Application.Transpose(Range("G1:G26")) MsgBox vArray(1) & " " & vArray(26) End Sub Note that to get column data into an array it must be transposed. For your particular example, the following code seems to work and is quite quick: Sub TestArray2() Dim AvArray() As Variant Dim BvArray() As Variant Dim GvArray() As Variant Dim HvArray(1 To 250) As Variant Dim AIndex As Integer Dim GIndex As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual AvArray = Application.Transpose(Range("A1:A250")) BvArray = Application.Transpose(Range("B1:B250")) GvArray = Application.Transpose(Range("G1:G250")) For GIndex = 1 To 250 For AIndex = 1 To 250 If GvArray(GIndex) = AvArray(AIndex) Then HvArray(GIndex) = BvArray(AIndex) End If Next AIndex Next GIndex Range("H1:H250") = Application.Transpose(HvArray) Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub It is obviously constrained to 250 rows as that was what your example was using. Note that this is 62500 comparisons. Regards Trevor "Stuart" wrote in message ... With a 2 column listbox, user is able to input values to column 2 via a text box. How may I test that all entries in col 2 have been made before the OkButton_Click enables them to leave the form, please? I am struggling to understand the basics of arrays, and know that it is important to do so. Here is very inefficient working code which I'm sure is ideally suited to arrays: For Each Cell In .Range("G1:G250") For Each c In .Range("A1:A250") If Cell.Value = c.Value Then Cell.Offset(0, 1).Value = c.Offset(0, 1).Value End If Next Next I would be very grateful for help with this particular situation, and also any links to primers on arrays. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.520 / Virus Database: 318 - Release Date: 19/09/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array to Populate ListBox Problem | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
Array Functions - Two Questions | Excel Worksheet Functions | |||
Array Questions | Excel Programming |