Inserting Values in a Multidimensional Array from Two Different Controls
 

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...o&userid=35137
View this thread: http://www.excelforum.com/showthread...hreadid=549692

Inserting Values in a Multidimensional Array from Two Different Co
 

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


"shivboy" wrote:


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...o&userid=35137
View this thread: http://www.excelforum.com/showthread...hreadid=549692

Inserting Values in a Multidimensional Array from Two Different Controls
 

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

Tom Ogilvy Wrote:
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


"shivboy" wrote:


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...o&userid=35137
View this thread:
http://www.excelforum.com/showthread...hreadid=549692




--
shivboy
------------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...o&userid=35137
View this thread: http://www.excelforum.com/showthread...hreadid=549692