Listbox and Array questions

[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.

 

[James S]

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.

18/09/2003

 

[Trevor Shuttleworth]

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]

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.

 

[Stuart]

Many thanks, both for the the explanation, and
the code for my example.

Regards.