Creating Multidimensional Array?
Feb 27, 2013
I want to create a multidimensional array. Basically, I want to tie a file to a sheet. I want to import the following files:
"byemployee.csv",
"byposition.csv",
"Status report.xls",
"bydepartment.csv",
"byband.csv"
to the following sheets in my workbook:
"byEmployee",
"byPosition",
"statusReport",
"byDepartment",
"byBand"
Basically, I would like to pass the array by reference. Basically, the code below imports the file into my workbook, but I since I have several files going into sheets in the workbook, I don't want to write the code 5 times. I figured the best way would be to create a multidimensional array and pass through my procedure below.
Code:
Sub import_Employee_Data()
strSourceFile = ThisWorkbook.Name
strPath = ThisWorkbook.Path & ""
strFirstImportFile = strPath & "byemployee.csv"
sDestSheet = "byEmployee"
If Len(Dir(strFirstImportFile)) > 0 Then
[code]......
View 8 Replies
ADVERTISEMENT
Feb 28, 2007
I’m working on a project where a spreadsheet holds data which is spread over multiple rows. The number of rows can change depending on the case.
Imagine 1 case being 1 row of certain details, then a various number of rows below detailing some different information in different columns.
A crude example is this: ...
View 9 Replies
View Related
Mar 9, 2007
I'm getting a compile error "Can't Assign to array" when I try to return a multidimensional array from a function. I'm not a VB expert, and I was unable to find any examples of returning a multidimensional array from a function. Here is some sample
Sub someSub
Dim data(1 To 4, 0 To 23) As Integer
For counter = 1 To numSheets
data = processData(counter, data) ' The error happens here
Next
End Sub
Private Function processData(counter As Integer, data() As Integer) As Integer()
Dim dataCopy(1 To 4, 0 To 23) As Integer
dataCopy = data
'modify dataCopy
processData = dataCopy
End Function
View 6 Replies
View Related
Mar 19, 2014
I have 9 named ranges on worksheet Sheet1. I want to print every combination of every non-singular range on worksheet Sheet2. Below is a simplified version of the scenario.
There are three named ranges: Letters, Colors, Animals. Say the below are the entries for each range.
Letters = {A, B, C}
Colors = {Red, Blue}
Animals = {Dog}I want to print every combination of Letters and Colors but exclude Animals since it only has 1 entry.
Therefore my result would look something like this:
A Red
B Red
C Red
A Blue
B Blue
C Blue
My thought is to make a multidimensional array GrandArray where GrandArray(1) = Letters and GrandArray(2) = Colors, then recursively go back through every combination and print to Sheet 2. I can set up GrandArray, but stepping through each element is creating mismatch errors.
I'm trying to avoid For loops since my real data has 9 ranges which may or may not be included in the final print.
View 1 Replies
View Related
Jul 15, 2014
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code:
Dim diffArray() As Integer
Dim countArray() As Integer
Redim countArray(count,2)
diffArray = getRunningSum(countArray)
End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer()
'Code here......
End Function
View 9 Replies
View Related
Dec 31, 2008
I have data in a dictionary object and need to load it into a two column listbox. The VBA help says you can load data into a multicolumn listbox from a 2D array. So my question is how can I extract the data from a dictionary object directly into a 2D array.
The dictionary object stores data in key and item pairs. So the 2D array would have one dimension for the keys and the other for the items. I want to do this without having to extract the items and keys into separate 1D arrays and then loop through them to build the 2D array.
View 9 Replies
View Related
Jul 28, 2006
I need to multiply matrix variable by a constant (each matrix entry has to be multiplied by the constant).
Sub Matrix()
Dim X As Variant, Y As Variant
Dim a As Integer
a = 2
X = [1, 1, 1; 2, 2, 2; 3, 3, 3]
Y = X * a ' Here it writes that type is mismached
End Sub
I read that in cell functions it is possible to do such calculations.
View 4 Replies
View Related
Jan 23, 2009
Rather than looping through a multidimensional array to populate a worksheet, is there a quick function which can export the entire array to a worksheet?
View 2 Replies
View Related
Jul 18, 2014
How to apply a filter over the array "a" shown below and get the result in a new array "b" containing the filtered values based on the following conditions (the conditions criteria could be 1, 2 or 3. In this case only 2):
Criteria1 in Column 4="yellow"
Criteria2 in Column 3="ggg"
And only show values of columns 1 and 3.
If were using an SQL query would be something like this:
Code:
SELECT F[1],F[3] FROM "Table" WHERE F[4]="yellow" and F[3]="ggg"
The output array would be as below:
Code:
b=[{12,"ggg";140,ggg}]
this is the array:
Code:
Sub test()
a = [{"122","53","ggg","yellow";"140","9","ggg","yellow";"16","-22","ddc","yellow";"127","-37","ddc","green";"53","-28","ggg","grey"}]
'Filter code to get array b
'
'
End Sub
View 1 Replies
View Related
Nov 28, 2011
I'm new to arrays. They seem promising for what I want to do though...
Heres where I'm at. I have some data like this:
items freq 1 0.5 2 0.5 3 0.5 4 0.25 5 0.25 6 0.125
Now, I've created a 2 dimensional array, as such:
Code:
Sub testarray()
Dim arr1 As Variant
Dim rng1 As Range
Dim lngX As Long
Set rng1 = [A2:A6]
ReDim arr1(1 To rng1.Count, 1 To 2)
For lngX = 1 To rng1.Count
arr1(lngX, 1) = rng1.Cells(lngX, 1)
arr1(lngX, 2) = rng1.Cells(lngX, 2)
Next
End Sub
Now, what I need to do, is create an array for each freq of all the items that share that freq. Essentially I need this:
0.5 {1,2,3} 0.25 {4,5} 0.125 {6}
Now, I was thinking, if I could create a dictionary object and make the key the freq (so my keys would be 0.5,0.25,0.125) then I could assing the "item" an array (or another dictionary) holding the items that apply to that freq.
View 9 Replies
View Related
Jul 7, 2014
I am trying to create a list from an array. Said array contains formulas that return numbers from input contained in an other table. I would like the list to list the numbers in ascending numerical order.
View 5 Replies
View Related
Mar 5, 2008
I am trying to do is, to sum up the values in an array, given that the cell value is not an error.
If the cells were in order, the following array formula would solve it easily:
{SUM(IF(ISERROR(A1:A3),0,A1:A3))}
But my problem is that, my cells are not in order. To be more specific, I want to look at A1, B12 and C13, and sum them up with an array formula given the condition that cell value is not an error. Of course, in my case, I have too many cells.
View 11 Replies
View Related
Mar 23, 2012
I need to create multiple arrays arr_1(), array_2(), array_3() etc. Each array will be used to store different data.
My idea was to create a for loop like the one below, which doesn't work.
Code:
For i=1 to 180
String = i
then initialize the array
Dim arr_& String ()
Redim arr_& String(1 to 183)
Next i
View 4 Replies
View Related
May 11, 2009
how to tackle a piece of work that I really don't want to do.
We have extracted some summary information on one of our products. The workbook has 10 sheets, one for each of our 10 main sales channels. Each sheet has the same layout of information on. There are 7 tables on each sheet, and each one cuts the data in a different way (eg one summarises by age of purchaser (in age bands), another by demographic group etc). Each table then has the same 14 columns of key data (eg number of sales, average order value).
I've been asked to analyse this information "for anything interesting". At the moment, the only way I can think to do this is to print out the 10 sheets, sit down with a highlighter, and try to visually identify trends and anomalies. The idea fills me with dread, and I suspect will be quite inefficient.
I've asked whether I can get the data in a pivot table or some other format that might be more conducive to analysis, and have been told no. (There will easily be more than 100,000 lines in the original data (we're using Excel 2003)). To be honest, I'm not sure that I'd really be that much better off, even if it were in a pivot table.
View 9 Replies
View Related
Feb 3, 2014
Lets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
Edit: I found that =norminv(rand(),,) will work.
View 1 Replies
View Related
Jan 8, 2010
i am trying to create an Array that will print the stock number as a title on row A. e.g. "Stock 1" in A2 "Stock 2" in A3...etc. here is what i have attempted:
View 2 Replies
View Related
Jan 17, 2014
I had some number combinations as this 5-23-34-233, 50-233-34-45, 34-5-23-45-67. The length can be variable in column A.
I want a formula which should return array of numbers of each cell in column A. So if A1 has 5-23-34-233 so the formula should return array like {5,23,34,233} and like wise.
View 8 Replies
View Related
Jan 22, 2009
[Sheet] .....
I have the above sheet, what i need to do is pull all records to a seperate sheet. The records need to be between the start and end dates and have no full reply. Please help. I am using excel verion 2003.
View 11 Replies
View Related
Feb 14, 2009
if someone have a code for filter o limit a Listbox but with 6 columns and 6 textbox because i have a code but is only onedimensional works fine but i dont have any clue how to modify to works like multidimensional
this the one dimensional
Private Sub tbxFind_Change()
Dim vList As Variant
'Read the original list
vList = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
'Convert it to a 1D array
vList = Application.Transpose(vList)
'Filter it
vList = Filter(SourceArray:=vList, _
Match:=tbxFind.Value, _
Include:=True, _
Compare:=vbTextCompare)
'Send it to the listbox
lbxCustomers.List = vList
End Sub
Private Sub UserForm_Initialize()
'Read the original list
lbxCustomers.List = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
End Sub
View 9 Replies
View Related
Oct 11, 2006
I have written a user type for my arrays but I am having trouble storing it. The number of arrays varies.
Dim arrTransactions() As Transaction
Dim assortedrowindex As Integer
For assortedrowindex = 2 To 100
Redim Preserve arrTransactions(0)
arrTransactions(0).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(0).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(0).BuyCurncy = Cells(assortedrownindex, 2)
arrTransactions(0).SelCuurncy = Cells(assortedrowindex, 10)
arrTransactions(0).Fund = Cells(assortedrowindex, 7)
arrTransactions(0).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(0).BuyUnits = Cells(assortedtrowindex, 15)
arrTransactions(0).FxRate = Cells(assortedrowindex, 16)
View 3 Replies
View Related
Nov 27, 2008
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 4 Replies
View Related
Jul 30, 2012
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
View 1 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Dec 20, 2013
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
View 4 Replies
View Related
Oct 2, 2008
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies
View Related
Apr 7, 2009
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View 6 Replies
View Related
May 2, 2013
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
View 9 Replies
View Related
Aug 5, 2014
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
View 8 Replies
View Related
Aug 26, 2009
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
View 6 Replies
View Related
Jun 21, 2014
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies
View Related
