Listbox and Array questions

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

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.
 
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.
Click to expand...
18/09/2003
 
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
 
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.
 
Back
Top