Multi-dimensional Array Not Showing In Listbox
I have made the function below to return a variant multidimensional array. I pass the function an array of folder paths that I wish to search through looking at subfolders within that path where their name matches a search string that I pass to the function. eg., it will find a folder named "Catnap" if you pass the string searching for as "Cat*".
The size of the array it builds depends on how many folders it finds that match the search string and so needs to be built dynamically. Hence, I believe it builds a 2 dimensional array horizontally and I transpose it at the end of the function. In each element I put the folder name that was found in the first dimension and the path to that folder in its second dimension.
I have a 2 column listbox on a form that I set this array to. eg., Me.ListBox1.List = DirPaths("C:","Cat*",vbDirectory,100)
This works fine and shows a list of folder items found by folder and path in the two columns of the listbox if there is greater than 1 search found. However, when the search only finds 1 then the listbox shows the folder name with the folder path in row 2 of the list box. (See below).....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Summing In A Multi Dimensional Array
I've created an array that is 60 pairs of cells wide. Each row in the array represents a different account. The 60 pairs of cells contain up to 60 payments in a payment stream. The first cell of each pair may contain an amount of interest to be paid, or zero if the time band in which it was/will be paid is not within the time range that I'm evaluating. The second cell of each pair may contain a number between 1 and 10 representing the time band in which the interest will be paid, or zero if the payment date falls outside of the time bands. I need to be able to sum the interest to be paid in each time band for all of the accounts in my database. Is there an Excel function that I could use or will I need to write some VBA code to loop through each row?
View Replies!
View Related
Multi-dimensional Array Solution
I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level: I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red). Here is my existing Keyword highlighting Sub Highlight_Keywords() Dim vntWords As Variant Dim lngIndex As Long Dim rngFind As Range Dim strFirstAddress As String Dim lngPos As Long
View Replies!
View Related
Populating A Multi-dimensional Array
I have a spreadsheet which I will be adding to over time. I need a macro that will look for the last 10 rows and then pull together a summary table based on data from several of the columns on each of those rows. I've tried to go about this by using an array. The array will always be the same size (it will always be 10 by 11, i.e. EngineArray(10,11)-basically the same size as my summary table). I've also set Option Base 1. What I’ve got so far is below. It isn’t working properly though. To start with I was getting ‘type mismatch errors’. Now instead of reading the numbers from the selected cells into the array, it just changes all the cells I’m trying to read from to ‘TRUE’?
View Replies!
View Related
Evaluate Multi-Dimensional Array Matrix
I'm trying to use VBA to do some matrix processing. I have successfully done matrix processing in the spreadsheet, but I'm looking to port the logic into VBA to more easily reuse central formulas and reduce the amount of code in the spreadsheet. The function shown below works. I had to hack around the variable declarations to eliminate processing/compile errors but it seems to function (no pun intended). While debugging I noticed that this function would seem to get called 4 times. I do have other modules running, although I don't think any other modules are affecting this. After some input from shg, I updated the variable declarations, but I'm getting an "Overflow" error reported. Sometimes (not always) a divide by zero also pops up. I do have the Msgbox for errors at the end, I haven't tried removing that part to see if the error reporting vanishes. I'm not sure 'hiding' error alerts is the best thing to do anyway...
View Replies!
View Related
Fill Range From Multi-dimensional Array
I searched and found a few posts about transposing arrays into a range of cells, but none of them seemed to solve my problem. So, my problem is, I have a .Net assembly which provides various functions to allow Excel to access our Oracle DB stored procedures/tables, etc. This assembly is exposed via COM Interop. I call the GetSPINTypes() method, which returns me a list of type pairs (ID, Name), in a CSV string format. I split the CSV into rows, and then put each row into a 2-dimensional array. I then need to dump that array into one of my sheets in Excel, so I try to do the usual Range.Value = Array, but this sometimes tells me there is a type mismatch, and most times just doesn't fill the range. I've checked my arrays in the watch window, and they have definitely been filled in correctly, the values just don't appear when they are put into the sheet. See the code I'm using below:......
View Replies!
View Related
Join Dynamic Multi-dimensional Array
the built in "Join" function can join all elements of a 1-dimensional array into a string with delimiter. Now, how do I do that with multi-dimensional array if I just want to join 1 dimension of it. For instance, I have: m = 10 n = 20 Redim my(m, n) 'assign values to array here... 'I want to join, say, my(5, 0 to n) only 'How to do that without declaring a new 1 dimension array? Also, I want to write a join sub/function to do the above for n-dimensional array, do I need to write each one for every number of dimension (1 sub for 2-dimension, 1 sub for 3 dimension, etc.)?
View Replies!
View Related
Fill & Rearrange Multi-dimensional Array From Another
I've got the folowing array's date1>company1>price >Company2>price >company3>price >enz Date2>company1>price >company2>price >enz enz. But these are not the array's that i need for a correlation that i want to make. Is it possible to transform the array's above to an array such as: Company>date1>price >date2>price >date3>price >enz
View Replies!
View Related
Creating A 2-dimensional Array From A 1-dimensional List
I've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate. What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.
View Replies!
View Related
Checking If Variable Is Multi-dimensional
Is there a quick way to check if a variable is an array or multi-dimensional? I am trying to send variables to a sub to print to excel but I am sending mixed variables - some are one dimension, some are two dimensional. Here is what I am using: Private Sub Send(Item As Variant, Top As Integer, ToSheet As String, Row As Integer, Column As Integer) 'Sends values to Excel ' Loop counter Dim i As Integer For i = 0 To Top - 1 ActiveWorkbook. Sheets(ToSheet). Cells(i + Row, Column).Value = Item(i) Next End Sub I suppose I could set up a second routine to handle one dimensional variables, but I thought I would ask the pros -
View Replies!
View Related
Lookup Values From A Multi Dimensional Table / Data Sheet
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex) On the report I have values 123A and 1234A On the data tab I have table that has. Ref Tot Value Desc Value1 Value2 Value3 1 123A Widgets 1 2 3 2 123A Widgets 4 5 6 Ref Tot Value Desc Value1 Value2 Value3 1 123B Nuts 7 8 9 5 123B Nuts 1 3 5 Ref Tot Value Desc Value1 Value2 Value3 7 1234A Bolts 2 4 6 11 1234A Bolts 3 5 8 So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View Replies!
View Related
Interpolate Two-dimensional Array
With a known X and Y, trying to solve for Z from a table. ie: IF a variable X is defined across a row, another variable Y defined down a column, the data field Z fills in-between. What do I need to use to interpolate for both X and Y to solve for Z?
View Replies!
View Related
Function For 2-dimensional Array
I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic: Public Function bondValue(years As Range, q_factor As Range, z As Double) Dim nRow As Long, nCol As Long Dim bondPort As Range nRow = q_factor.Rows.Count nCol = years.Columns.Count sumTau = 0
View Replies!
View Related
Fill ComboBox With 2 Dimensional Array
I can set up a 2 dimensional array by using array = Workbooks("Workbook.xls").Worksheets("Data"). Range("D_all") as range D-all contains 2 columns and 20 rows I can send that list to a combo box by using Me.ComboBox1.List() = array If I have ColumnCount set to 2 then both columns will be listed If I have ColumnCount set to 1 then the 1st part of the array will be listed But how do I list just the 2nd part of the array
View Replies!
View Related
Data Type Of Coordinates Of 2-dimensional Array
I have a big array "DataArray" and want to access it: For i = 1 To 4 variable = DataArray(SourceArray(i)) Next i "DataArray" has two dimensions, so SourceArray has to consist of data like this: SourceArray(1) = 1,2 sourceArray(2) = 2,4 etc What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.
View Replies!
View Related
Multi-Line Listbox
I am reading info from a sheet via array into a listbox which displays the results on a user form. One of the columns has a long text and I need to wrap the text. From my searches I think I need to have an actove x listbox. I have right clicked on the toolbox but can not see one with the name for active x lisrbox. what one I should select and if possible how to set it up to wrap the text.
View Replies!
View Related
Multi Select Listbox
I have a mulit-extended listbox, a textbox and a button on a form. I have populated the listbox with a list of files from a directory no problems. First Part (Single Selection): When I click on one/single filename in the list I want: 1. The textbox to be populated with the name I have selected 2. When I click the button (With the single filename selected, I want to call a function passing the filename I have selected. The function could contain a msgbox(selecteditemname) for now. Second Part (Multi Selection): When I multi select filenames from the list I want: 1. The textbox to be populated with "Multiple Files Selected" 2. When I click the same button as before (With multiple filenames selected, I want to call a different function and pass each filename individually in may be a for loop) The function could contain the loop and with each filename in the loop msgbox(selecteditemname) I have tried the above and noticed that the listbox1.value property for multi listboxes dont seem to work and I am not really sure how to distinguish between one selection or multiselection to get a button to pass off to two separate functions.
View Replies!
View Related
Preventing ListBox From Showing Blank Cells
if it is possible to prevent a listbox from showing blank cells. I have selected a big range and many of the cells are blank. The reason is that I want the listbox to update automatically when I add a value to any of the blank cells. Using the methods suggested by the posters here I was able to solve my problem. I had to modify my source data somewhat but it was well worth it.
View Replies!
View Related
Sorting A Multi-colomn Listbox
I have written a VB-excel program after a lot of reading on this forum. Purpose of the program is to load a number of log-files with lots of data. I load these files in a 2 column listbox, were the 1st column is the name of the file and the second column is a number, starting with 1, 2, 3 etc. Since I don't get the files in the order I want them to be, I created a button to change the number in the 2nd column. Now my problem: I need to sort the listbox according to the 2nd column. I have a routine that works ok, but whenever I make a mistake like duplicate numbers in the 2nd column, I lose all the data in the listbox and can start over again. Dim listarray(20, 2) As Variant, Log_name As String, k, i, n, m, listnum, pos, remove_num listnum = ListBox2.ListCount For n = 0 To listnum - 1 pos = ListBox2.List(n, 1) - 1 Log_name = ListBox2.List(n, 0) listarray(pos, 0) = Log_name listarray(pos, 1) = pos + 1 Next n.................
View Replies!
View Related
Multi Criteria Filtering Listbox
I am using the following piece of code to 'link' a value from a cell in one workbook into another. ActiveCell.FormulaR1C1 ="='[filepath]Front Sheet FIT'!r7c4" However, i have come across a cell which displays the result of a reference to another cell (something simple like =b32). Normally I would just link to b32, however, the macro has to be applied to a large number of documents and in some cases text has been specifically entered, while in others the reference has been used. Is there any way of using VBA to display either the result of a formula (if there is one) or text if this is the case?
View Replies!
View Related
Multi Column Listbox :: To Display A Number
I have a form with a list box on it, I want the listbox to display a number (1 through 20), the agents name, and a count of how many observations they have had. Unfortunately I have been unable to find an example of how to add multiple columns to a list box. I found the usual listbox1.list(x,x)= "whatever" type stuff but that doesn't seem to work right. how to populate multiple columns including the headers, or point me to a site that explains it or has code examples? Here is the data I want to put in the multicolumn listbox (the number and the agent name are from a worksheet, the deskside is a calculation sumproduct based on the 3 sheets that make up the quarter....
View Replies!
View Related
VBA For A Multi Select Listbox Results With NewLine
Is it possible to have some VBA in Sheet1: 1. That when a user click on a cell in a particular column within a specific sheet: 1.1 That they are presented with a multi select listbox (Showing 20 items stored in a column in Sheet2 ) 1.2 And when the items are selected that the items are automatically written in the same cell that was clicked in Sheet1 with a new line break in between each item?
View Replies!
View Related
Multi Select Listbox, Remove Selected Items.
i have a multi select listbox that i want to be able to remove the selected items in one hit. i have not been able to convert code for removing single selected items and could not find a working example. this is probley the closest though cos its removing items it plays havoc with the listcount
View Replies!
View Related
Removing And Replacing Items In Multi Column Listbox.
Ive been having lots of trouble programing a multi column listbox to play ball. On the form there is 1 tree view and two listbox's. The object is to double click a list 1 item and have that go into the list 2. Connected to the listbox2_dblClick.. Takes the selected list item and adds to columns 1 intergra value. This places a annoyance to me where listbox 2 is not fully populated (you will see ive fully populated the list during my own testing) and a item is selcted if double click on a blank part of the listbox it will increase the int value of the selected item. I though of populating the list with blanks (used "." as visual) so a cheek could be made on the double click item and if blank do nothing.....
View Replies!
View Related
Transfer Chosen Items From Multi-Select ListBox
I am trying to create a data entry form with a text field and a listbox. The list box is populated by another sheet and the text box will be used to enter names. I need to be able to enter a new name, select multiple options from the list box and then click a button to add this info to a sheet in excel. I have read about changing the list options to allow this etc - the problem is I can't get the results to go back into excel - all I get is a name with nothing from the listbox.
View Replies!
View Related
Reloading Selections In Multi Select Listbox Control
I have a multiselect listbox in a worksheet that the user can select from. When the file is saved, closed and reopened, the user selections disappear. Is there a way of reloading the previous selections when the file is reopened? I have used the listbox from the control toolbox and have Excel 2003. My VB experience is very limited!!
View Replies!
View Related
Add Items: Multi Column ListBox: Non-Contiguous Range
i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command Private Sub UserForm_Initialize() Dim contar, j, i As Double Sheets("Historico").Select contar = 0 For j = 1 To 15 linha = j For i = 3 To 65536
View Replies!
View Related
Indentify Selected Items In Multiple Multi Select ListBox Controls
changing two variables a various amount of times and running the same procedure and copying the resutls into another sheet. Seems like a perfect place for a macro. However, these variables can be chosen from a list that the user wants. So why not build in a listbox for each one. Now I have two listboxes one for variable A and one for variable B. The procedure in theory goes something like this we change variable A from the base case and then run the procedure for variable B, get the results, then run the scenario again but changing only variable B abnd repeat. Then once, all of the variable B scenarios are done, I want to change the variable A and then repeat and so forth. That is the background and my main problem at this point, is that have these values in two listboxes, I know how to do the for each loops and such, however, I do not know how to do them for values in the listbox. How do I identify the values selected in the respective listboxes and then pull them so I only use them for the for each loop?
View Replies!
View Related
Fill Multi Columned ListBox Based On Range Meeting 2 Conditions
I'm trying to populate a dropdown Combo Box by using conditions. In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels. I've been trying to use worksheetfuntion.Index.
View Replies!
View Related
Fill Multi Select ListBox With File Paths & Open Selected Files
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request. Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file. Sub HyperlinkXLSFiles() ActiveSheet.Unprotect Dim lCount As Long Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "c:Sec" .FileType = msoFileTypeExcelWorkbooks ' .Filename = " Book*.xls"..................
View Replies!
View Related
Multi-cel Array Formula Not Working
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour. Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
View Replies!
View Related
Entering Multi-cell Array Formulas
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1". Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
View Replies!
View Related
Multi-result Array Formula With Max
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into. Example for A1=2,B1=4 =2*A1:B1 array enetered gives 4,8 However =Max(2*A1:B1,5) array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
View Replies!
View Related
Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives. After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero). Listbox and expected result are in attached example worksheet. The attached does show the need much better then I can explain it.
View Replies!
View Related
Listbox Array
I'm trying to load an array for a list box using sheet "ZIP Codes", cells A3-A? for the ZIP Codes, and cells B3-B? for the city/town associated with the ZIP Codes. Example: A2 B2 00000 ThisCity 11111 ThisTown I need to load sheet "ZIP Codes" A3-A? as MyArray(i,0) and load B3-B? as Myarray (i,1), but after putting in soooooo many hours in at work I can't get my brain working right.
View Replies!
View Related
Array To Userform Listbox
I have been searching the internet and this sight and have found many variations, but I cannot seem to get my listbox on a Userform to populate. The array is 10 columns by 5 rows. I can get it to add one long column of data, but this is obviously now what I want.
View Replies!
View Related
Populate Listbox With Array
I've got a list box which I want to fill with a two column array, with items from a sheet based on a criteria selected by userform fired from another sheet. Private Sub VariationsApprovedListMake() Dim ws As Worksheet Dim MyList(10, 2) As String Dim M%, n% Set ws = Worksheets("Variations") LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row 'Clear ListBox lbVariationsApproved.Clear '> With lbVariationsApproved .ColumnCount = 2 .ColumnWidths = "25;25" .Width = 200 .Height = 100 End With.....................
View Replies!
View Related
Populating Array From Listbox
I am in the process of trying to load an array from items in a list box. The variable "sProd" is the value from the listbox and I want to populate the value in the array with the listbox value so ultimately I can generate SQL on the fly. Lets say 3 values are in the listbox and my array is loaded, I would then trnaspose the array a create a SQL statement like: SELECT PVT_STAGE_SOURCE_SSv2.[pool],PVT_STAGE_SOURCE_SSv2.[ball], PVT_STAGE_SOURCE_SSv2.[raft] FROM PVT_STAGE_SOURCE_SSv2 With Me.lstExclude For i = 0 To .ListCount - 1 sProd = .List(i) pArray ("PVT_STAGE_SOURCE_SSv2.[" & sProd & "]") Next i End With
View Replies!
View Related
Populate Listbox From An Array
I have a set of values stored in an array an I simply want to populate the list box with these. one article on the microsoft website simply gave: 'Assign the array to the listbox ListBox1.List = LArray However, I get an object required error.
View Replies!
View Related
Select Default Value In A Listbox (array)
I have a problem with a listbox on a userform. I have an array that is stored in a name. The array has 2 columns and + 1000 rows. It looks like this: 100 Name1 110 Name2 120 Name3 Etc. The listbox is filled from the array: Private Sub UserForm_Initialize() frmZoeken.lstbx_Gbr.Clear frmZoeken.lstbx_Gbr.ColumnHeads = False Dim myArr() myArr = Evaluate("varRekSchema") frmZoeken.lstbx_Gbr.List = myArr End Sub When the form with the listbox opens, it has to select by default the value in the listbox that corresponds with a value in a cell in the workbook: Private Sub UserForm_Activate() 'On error resume next frmZoeken.lstbx_Gbr.Value = Cells(ActiveCell.Row, 3).Value frmZoeken.txt_Zoekterm.SetFocus End Sub When opening the userform the following error message appears: Could not set the Value property. Invalid property value. When I activate the row "On error resume next" the listbox is correctly filled. The error occurs with selecting the default value.
View Replies!
View Related
Fill MultiColumn Listbox With Part Of Array
I have a multicolumn listbox, and a multidimensional array. I want to put just SOME values of the array into the listbox, but I ger errors using both .list or .column to access single items, and even using .additem . I have: dim variable(2000,2) as string variable(1,0)="aaa": variable(1,1)="bbb": variable(1,2)="ccc" variable(2,0)="www": variable(2,1)="awasd": variable(1,2)="asdfa" .... variable(2000,0)="www": variable(2000,1)="awasd": variable(2000,2)="asdfa" I don't want to store all 2000 elements, just some ones: how can I do it?
View Replies!
View Related
Populate Array From Mutiple Listbox Selections
Trying to load an array from user selected items in a listbox My code returns Type Mismatch error I don't know why as a I did not declare type for the array I thought treated as variant so should accept any values? Debug: arrSelected(intI) = .Selected(intI) Private Sub btnOK_Click() Dim arrSelected Dim intI As Integer With Me.lstAccounts For intI = 0 To .ListCount - 1 If .Selected(intI) Then arrSelected(intI) = .Selected(intI) End If Next intI End With
View Replies!
View Related
Dimensional Table
i have a problem with my report. there is a dimensional table: DIM RESULTSDIM_111.1DIM_111.8DIM_111.2DIM_25.3DIM_25.5DIM_380.2DIM_380.1DIM_380.35 i need to make a new table in wich all "dim" will repeat only once and the results will be the average of the results that belongs to the same "dim" the times that the "dim" repeats can change and the "dims" could be not only till 3
View Replies!
View Related
|
TRACKER
