Writing multicolumn array data to a worksheet range

[BobbyC163]

have an Excel worksheet (called MyWorksheet) where I am trying to select a
customer name and address from a list of all customers located on another
worksheet (CustomerWorksheet). I am using a multicolumn listbox on a userform
to select the customer data. Once selected, I want to capture the customer
data in a multicolumn array and then write the customer data to a worksheet
range in MyWorksheet.

The code I have written seems to be able to capture the selected customer
data (all columns) in an array but it will not write the data to the range in
the MyWorksheet. Any help would be greatly appreciated.

Thanks



Private Sub cmdOkay_Click()
Dim i As Long, j As Long
Dim lRows As Long, lCols As Long
Dim CustArray() As Variant


lRows = ListBox1.ListCount - 1
lCols = ListBox1.ColumnCount - 1

'Fill array with data of selected customer
For i = 1 To lRows
If ListBox1.Selected(i) Then
For j = 0 To lCols
ReDim CustArray(lRows, lCols)
CustArray(1, j) = ListBox1.List(i, j)
Next j
End If
Next i
Unload userform1

'Write array data to range on worksheet
With Worksheets("MyWorksheet").Range("CustomerData")
..ClearContents
..Value = CustArray
End With


End Sub

 

[RB Smissaert]

Any error message?
Try putting Unload userform1 at the very end of that Sub

RBS

 

[BobbyC163]

Thanks for the reply.

No error message appears. I did re locate the unload command as you
suggested but it made no difference.

I tried using a test array that I created as follows:
testArray = Array (1,2,3,4,5,6,7,8)

The data in the test array does get written into the worksheet range the way
I want using my code so I believe my problem has to do with the way i have
created my 2D array called CustArray.

 

[Patrick Molloy]

I amended the code...
issues:
1) REDIM clears th earray, you need to include teh PRESERVE keyword
2) yuo can redim only the LAST column of the array with a redim, so I you
will, your table grows sideways, so the ROW in the LISTBOX transposes to
COLUMN in teh arraw...vice-versa with the columns of the listbox
3) because the array is transposed, we need to transpose it again before
dropping to the sheet.




Option Explicit
Private Sub cmdOK_Click()
Dim i As Long, j As Long
Dim lRows As Long, lCols As Long
Dim CustArray() As Variant

Dim arw As Long


lRows = ListBox1.ListCount - 1
lCols = ListBox1.ColumnCount - 1
arw = 0
'Fill array with data of selected customer
For i = 0 To lRows
If ListBox1.Selected(i) Then
arw = arw + 1
For j = 0 To lCols
ReDim Preserve CustArray(0 To 1, 1 To arw)
CustArray(j, arw) = ListBox1.List(i, j)
Next j
End If
Next i
Unload UserForm1

'Write array data to range on worksheet
With Worksheets("MyWorksheet").Range("CustomerData")
..ClearContents
..Value = WorksheetFunction.Transpose(CustArray)
End With


End Sub

 

[Patrick Molloy]

here's a tidier method if you don't need the arraY::

Private Sub cmdOK_Click()
Dim i As Long, j As Long
Dim target As Range
'clear the table
With Worksheets("MyWorksheet")
.Range("CustomerData").ClearContents
Set target = .Range("CustomerData").Resize(1, 1)
End With
'populate table from listbox
With ListBox1
For i = 0 To .ListCount - 1
If ListBox1.Selected(i) Then
target = .List(i, 0)
target.Offset(, 1) = .List(i, 1)
Set target = target.Offset(1)
End If
Next i
Unload UserForm1
End With
End Sub

 

[BobbyC163]

Awesome. It worked.

My customer list was 8 columns so I just changed your ReDim Preserve
parameters to (0 to 8, 1 to arw) and everything worked.

Thank you for the help.

 

[RB Smissaert]

Ah, yes, didn't look at that properly, but I see you have this solved now.

RBS