Arrays

[Guest]

I am doing a mcaro where i want to take a slected range of cells and convert
it to an array so i can add it to a list box on a user form. The problem is
once I get my range of cells selected I have no idea on how to convert it to
an array and make a variable such as intAdd equal to that array so I can add
it to the list bow. Can anyone help????

 

[Guest]

Hi Brendan Vassallo,

Following sample code will copy A1:A10 in Sheet1 to MyArray and also
populates to lstPopulate.

===================================================
Private Sub cmdPopulate_Click()
Dim MyArray(11) As String
Dim Check As Boolean
Dim Counter, NumberOfItems As Integer

Counter = 1
Check = True
Do While Check = True
If Worksheets("Sheet1").Cells(Counter, 1).Value <> UCase("STOP") Then
MyArray(Counter) = Worksheets("Sheet1").Cells(Counter, 1).Value
Counter = Counter + 1
Else
Check = False
End If
Loop

NumberOfItems = Counter
For Counter = 1 To NumberOfItems
lstPopulate.AddItem MyArray(Counter)
Next Counter

End Sub

===================================================

However I have pasted below the complete code for userform for you.

===================================================
VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} Populate
Caption = "Populate"
ClientHeight = 4425
ClientLeft = 45
ClientTop = 435
ClientWidth = 5040
OleObjectBlob = "Populate.frx":0000
StartUpPosition = 1 'CenterOwner
End
Attribute VB_Name = "Populate"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmdPopulate_Click()
Dim MyArray(11) As String
Dim Check As Boolean
Dim Counter, NumberOfItems As Integer

Counter = 1
Check = True
Do While Check = True
If Worksheets("Sheet1").Cells(Counter, 1).Value <> UCase("STOP") Then
MyArray(Counter) = Worksheets("Sheet1").Cells(Counter, 1).Value
Counter = Counter + 1
Else
Check = False
End If
Loop

NumberOfItems = Counter
For Counter = 1 To NumberOfItems
lstPopulate.AddItem MyArray(Counter)
Next Counter

End Sub

Private Sub cmdQuit_Click()
End
End Sub

===================================================

With kind regards

NAVEEN

 

[Guest]

Actually, your range object is an array. You can access individual cells by

Selection(3,2).Value

You should be able to just drop your selection object into the listbox.

Sub test()
UserForm1.ListBox1.Clear
UserForm1.ListBox1.List = Selection.Value
UserForm1.Show
End Sub

 

[Guest]

thanks guys...that worked

 

[Guest]

you're welcome.

thanks guys...that worked