Inserting Values in a Multidimensional Array from Two Different Controls

[shivboy]

Hi,

I'm using a userform to capture data and there on display them in
various cells in a sheet. For storing the data before displaying in the
cells, I am using a multidimensional array. Now, the problem I am facing
is that I have a combobox and a listbox whose values I want to keep on
adding into the multidimensional array till the user clicks the submit
button. The code I am using is :


Code:
--------------------

Dim arr() As String
Dim oName As String
Dim oJob As String
Dim cName As Integer
cName = lbColNames.ListCount
oName = txtColName.Value
oJob = cbJob.Value
If cbJob.Value = "" Then
MsgBox "Please select a Job."
cbJob.SetFocus
Else
lbColNames.AddItem (oName)
ReDim Preserve arr(cName, cName)
arr(cName, cName) = oName
arr(cName, cName + 1) = oJob
End If

--------------------


But this generates an error "Subscript out of range". What wrong am I
doing here? And how to insert values from 2 different controls into one
multidimensional array? Please help.

Peace,

Shivboy

 

[Tom Ogilvy]

You redim'd the array to

ReDim Preserve arr(cName, cName)

then try to add a value to

arr(cName, cName + 1) = oJob

[cname + 1 is greater than the upper bound of cname]

which should give you the error you describe.

 

[shivboy]

Hi Tom,

Thanks for replying, but I am still unable to understand what I need to
be doing to fix the problem. Could you please help me out with it?

Peace,

Shivboy

You redim'd the array to

ReDim Preserve arr(cName, cName)

then try to add a value to

arr(cName, cName + 1) = oJob

[cname + 1 is greater than the upper bound of cname]

which should give you the error you describe.

--
Regards,
Tom Ogilvy

Hi,

I'm using a userform to capture data and there on display them in
various cells in a sheet. For storing the data before displaying in the
cells, I am using a multidimensional array. Now, the problem I am facing
is that I have a combobox and a listbox whose values I want to keep on
adding into the multidimensional array till the user clicks the submit
button. The code I am using is :


Code:
--------------------

Dim arr() As String
Dim oName As String
Dim oJob As String
Dim cName As Integer
cName = lbColNames.ListCount
oName = txtColName.Value
oJob = cbJob.Value
If cbJob.Value = "" Then
MsgBox "Please select a Job."
cbJob.SetFocus
Else
lbColNames.AddItem (oName)
ReDim Preserve arr(cName, cName)
arr(cName, cName) = oName
arr(cName, cName + 1) = oJob
End If

--------------------


But this generates an error "Subscript out of range". What wrong am I
doing here? And how to insert values from 2 different controls into one
multidimensional array? Please help.

Peace,

Shivboy


--
shivboy
------------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35137
View this thread: http://www.excelforum.com/showthread.php?threadid=549692