Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use an array and a range
Im having difficulty managing an array and a range in my program. I want
the user to choose a whole range on a worksheet, which I assign to a range variable, or to select a range in a list box on a form. I populate an array with the users selection in the list box€”at least I think I do, Ive little experience with arrays. This way the user can select all the items or choose a smaller range in the list box. Then I want to take the users selection, either all the items or the list box selection and iterate through those items. This code loads form and manages list box: sCostCentreSelection = MsgBox("To run all Cost Centres select Yes" & vbCrLf & "Select No to choose Cost Centres" & vbCrLf _ & "Choose range on sheet", vbYesNo, "Select Cost Centres to run") If sCostCentreSelection = vbNo Then frmCostCentres.Show ' *** code to set rngCostCentres to array*** Else Set rngCostCentres = Range("inpCostCentres") 'this is a range on the Matrix sheet for all the cost centres End If This code is from form: Private Sub cmdOK_Click() 'test code to extract list box cost centre selection 'arrayCostCentres in general declarations calling procedure Dim sMsg As String Dim iCounter As Integer Dim sResponse As String sMsg = "" For iCounter = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(iCounter) Then _ sMsg = sMsg & ListBox1.List(iCounter) & vbCrLf ReDim arrayCostCentres(0 To ListBox1.ListCount) arrayCostCentres(iCounter) = ListBox1.Selected(iCounter) Next iCounter sResponse = MsgBox("You Selected: " & vbCrLf & sMsg & vbCrLf & "Click Yes to accept range" & _ vbCrLf & "Click No to select another range" & vbCrLf & "Click Cancel to halt program", vbYesNoCancel, "Cost Centres") If sResponse = vbYes Then MsgBox "Run Cost Centres" Unload frmCostCentres ElseIf sResponse = vbCancel Then End End If End Sub Any help appreciated. Thanks, Jake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving data from an array to a range when range consists of areas? | Excel Programming | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Specifying a range within an array | Excel Programming | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |