Inserting Values in a Multidimensional Array from Two Different Controls

S

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
 
T

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

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

Tom said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top