Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying a 1d Array in a spreadsheet and a list-box
Hello
I have an array ListSubFldrs that is a result of a macro that searches for sub-directory names within a folder. It can vary in size depending on how many sub-directories there are. eg. copied from Local Window... ListSubFldrs : ",Cat,Barclay Mowlem,Allflex,AWU,Burdekin Shire Council,Business Success Group,Cash Sales,Colorado,Flight Centre,Foot Locker" : Variant/String Any suggestions for code that would result in a vertical list in the .xls starting at A1. eg. Cat Barclay Mowlem Allflex AWU Burdekin Shire Council Business Success Group Cash Sales Colorado Flight Centre Foot Locker How do I then get this list into a list box within a form? Do I refer to the ..xls or the Array 'ListSubFldrs'? Thanks, Andrew Heath |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying a 1d Array in a spreadsheet and a list-box
Add this function,
Public Function ReturnArray() As Variant ReturnArray = Array("Cat", "Barclay Mowlem", "Allflex", _ "AWU", "Burdekin Shire Council", _ "Business Success Group", "Cash Sales", _ "Colorado", "Flight Centre", "Foot Locker") End Function then on the worksheet, select the target cell, and enter =TRANSPOSE(ReturnhArray()) as an array, Ctrl-SHift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Andy" wrote in message ... Hello I have an array ListSubFldrs that is a result of a macro that searches for sub-directory names within a folder. It can vary in size depending on how many sub-directories there are. eg. copied from Local Window... ListSubFldrs : ",Cat,Barclay Mowlem,Allflex,AWU,Burdekin Shire Council,Business Success Group,Cash Sales,Colorado,Flight Centre,Foot Locker" : Variant/String Any suggestions for code that would result in a vertical list in the .xls starting at A1. eg. Cat Barclay Mowlem Allflex AWU Burdekin Shire Council Business Success Group Cash Sales Colorado Flight Centre Foot Locker How do I then get this list into a list box within a form? Do I refer to the .xls or the Array 'ListSubFldrs'? Thanks, Andrew Heath |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying a 1d Array in a spreadsheet and a list-box
If you want to use the result as a function (as in Bob's response), you
will need to select as many (or more) cells as you expect in your response. One possible (uncompiled) way to code this would be: function myFunction(...) ... If Application.Caller.Columns.Count1 then myFunction="This can be used only on a single column range" elseif application.Caller.Rows.Count<ArraySz(myArray) then myFunction="You need at least " & ArraySz(myArray) _ & " cells for this result" else myFunction=application.worksheetfunction.transpose (myArray) end if end function If this code is in a subroutine, one way to code would be: myDest.resize(ArraySz (myArray),1).value=application.worksheetfunction.t ranspose(myArray) as in Function ArraySz(anArr) ArraySz = UBound(anArr) - LBound(anArr) + 1 End Function Sub testIt2() Dim myArray, myDest As Range myArray = Array("Cat", "Barclay Mowlem", "Allflex", _ "AWU", "Burdekin Shire Council", _ "Business Success Group", "Cash Sales", _ "Colorado", "Flight Centre", "Foot Locker") Set myDest = ActiveCell.Resize(ArraySz(myArray), 1) If Application.WorksheetFunction.CountA(myDest) = 0 Then myDest.Value = _ Application.WorksheetFunction.Transpose(myArray) Else MsgBox "Insufficient empty cells in column starting with " _ & ActiveCell.Address & " to return result of " _ & ArraySz(myArray) & " elements" End If End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hello I have an array ListSubFldrs that is a result of a macro that searches for sub-directory names within a folder. It can vary in size depending on how many sub-directories there are. eg. copied from Local Window... ListSubFldrs : ",Cat,Barclay Mowlem,Allflex,AWU,Burdekin Shire Council,Business Success Group,Cash Sales,Colorado,Flight Centre,Foot Locker" : Variant/String Any suggestions for code that would result in a vertical list in the .xls starting at A1. eg. Cat Barclay Mowlem Allflex AWU Burdekin Shire Council Business Success Group Cash Sales Colorado Flight Centre Foot Locker How do I then get this list into a list box within a form? Do I refer to the .xls or the Array 'ListSubFldrs'? Thanks, Andrew Heath |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying a 1d Array in a spreadsheet and a list-box
Option Explicit
Sub AA() Dim LstSubFldrs As Variant Dim lngth As Long LstSubFldrs = Array("Cat", "Barclay Mowlem", "Allflex", _ "AWU", "Burdekin Shire Council", _ "Business Success Group", "Cash Sales", _ "Colorado", "Flight Centre", "Foot Locker") UserForm1.ListBox1.List = LstSubFldrs UserForm1.Show lngth = UBound(LstSubFldrs) - LBound(LstSubFldrs) + 1 Range("A1").Resize(lngth, 1).Value = Application.Transpose(LstSubFldrs) End Sub -- Regards, Tom Ogilvy "Andy" wrote in message ... Hello I have an array ListSubFldrs that is a result of a macro that searches for sub-directory names within a folder. It can vary in size depending on how many sub-directories there are. eg. copied from Local Window... ListSubFldrs : ",Cat,Barclay Mowlem,Allflex,AWU,Burdekin Shire Council,Business Success Group,Cash Sales,Colorado,Flight Centre,Foot Locker" : Variant/String Any suggestions for code that would result in a vertical list in the .xls starting at A1. eg. Cat Barclay Mowlem Allflex AWU Burdekin Shire Council Business Success Group Cash Sales Colorado Flight Centre Foot Locker How do I then get this list into a list box within a form? Do I refer to the .xls or the Array 'ListSubFldrs'? Thanks, Andrew Heath |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Taking the Zeros out of an array when displaying | Excel Discussion (Misc queries) | |||
Spreadsheet not displaying | Excel Discussion (Misc queries) | |||
Pie chart not displaying % in spreadsheet | Charts and Charting in Excel | |||
Displaying URL images in a spreadsheet | Excel Discussion (Misc queries) | |||
Need help displaying array contents | Excel Programming |