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
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
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.
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
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.
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?
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"}]
I have come up with the following array UDF to help me split a large list of values into list of smaller groups.
The function takes the 3rd input argument to to understand how many smaller lists it needs to split the bigger range. The 2nd argument simply specifies a seperator, either "," or ";"
On using the same on a worksheet I'm getting a #VALUE! error and for the life of me am not able to pinpoint the problem area.
Public Function CBOList(Rng As Range, App As String, OutRng As Range) As Variant
Dim Func As WorksheetFunction, UniqueVals As New Collection Dim cnt, who Dim ctr As Long, lst As String Dim myarr() As Variant
I know I can do this as I have already succeeded once but since moving a few things on my spread sheet the formula has gone wrong and I can't correct it.
I have one sheet with many different lines on and another I would like to select certain rows with the same reference number in column A. Below is the formula I am using
I have an Excel spreadsheet (XP - 2007) listing Job Nos. in the first column, with several columns of Station assignments and dates. Both planned dates and actual dates are included, adjacent to each other. The dates are not necessarily in a straight ascending or descending order. Separate arrays exist for: Plan Nametags, Actual Nametags, Plan Dates, Actual Dates.
Example:
Job No.Sta1 PlanSta1 ActSta2 PlanSta2 ActSta3 PlanSta3 Act1A10001-Feb-101-Feb-106-Feb-106-Feb-101-Mar-101A100116-Feb-1016-Feb-1019-Feb-1022-Feb-105-Mar-101A100225-Feb-1025-Feb-102-Mar-104-Mar-1010-Mar-10@ @ I need to capture two pieces of information for each Job No. on a daily basis: 1- The 'Planned' Station for the build, based on a match of the 'Plan' date fields to a pre-populated 'Report Date'. I've been able to do this (using INDEX-MATCH function). 2- The 'Actual' Station location for the build, based on the Maximum 'Actual' date entry in the row for each build. (In the example above, Job# 1A1001 would have an 'Actual' location of 'Sta2 Act'...) I need to figure out how to accomplish step (2) above. I've made several attempts with INDEX-MATCH and LOOKUP functions, without success.
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.
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)
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.
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.
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:
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.
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.
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.
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.
Sub Macro2() For y = 29 To 34 For x = 2 To 7 Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18) Next x Next y End Sub Sub Teste12() 'Multiplica matriz em TestB por matriz padrão em TestA com _ 'resultado na matriz em TestA correspondente à matriz em TestB ' Dim x, y As Integer For y = 2 To 7.............
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.
