I have a dimensional array / matrix that is current a 9 by X, where x depends on the amount of entries a user makes.
Anyways my question is that I have a sheet called "Database" and I wanted to know if there was a simple code to transpose my array on that sheet starting at cell "A2".
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.
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.
i have a sheet in template i use in preparing bid packages for electrical installations.it relates to locating in a (building) grid the locations of the motor control (mcp) and power panels (cdp).my desire at this point is to find a way to local a unique value (mcp or cdp) in a 2d grid and then return the column and row names from the same grid. eventually this information will populate a table of all the mcp's and cdp's with their locations.
my sticking point appears to be finding a unique value in a 2d table. this value can be anywhere in the table, not just the first column.
i've tried the lookups and index/match. match fails when i extend the lookup_array beyond the first column.
not sure if this is even possible.
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 9 Replies View RelatedI 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
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’?
Filling all elements of a two dimensional array?. I know that I could write something like:
View 3 Replies View RelatedI'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?
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
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
I had this code and it works like a charm, except, it crashed if the data gathered is just one element due to transpose ..
VB:
Function getAR(c1 As Range, c2 As Range, w As String) As Variant
Dim s As String
Dim arrTemp() As Variant
Dim arr() As Variant
[Code]......
What I'm trying to do:
1) I have data that has a header row of 6-10 values (created, action, type, id, ...). The rows are unique records.
Click here to see the data
2) I originally wanted to read the data into one array (DataArray) and the header into another array (KeyArray). Then I would add additional unique information about each record (additional columns of info) into the DataArray.
3) I stumbled upon Dictionaries as a way of storing key/item pairs, which I thought could apply to each record since the headers are all unique and would be a way of storing key/items without needing to:
a) Know the ordering (if I used arrays I had to know the upper bound of the array to insert a column
b) Look up which what position in the array mapped to which column header
c) Resize the array every time I wanted to insert a new column of information on all the records
4) So now that I know about a dictionary object, I believe I need to create one Dictionary object for EACH record (row) of my data and then store those Dictionary objects within a one dimensional array such that each element of the array is a Dictionary.
5) This way I can iterate through each element of the array to access the dictionary inside and perform calculations on each record, depending on which key/item I needed to work with at a later point
Questions for Dictionary experts:
1) Is my approach sound?
2) What is the syntax for putting the dictionary of one row's worth of data into an element of a one-dimensional array?
I'm trying to make this piece of code work to no avail yet... Every time I run it, the compiler pops up with Error #9: "Subscript out of range". The range consists of hundreds of cells in 1 column with geographic coordinates formatted as "General". Like this: myArr(38.91472, 37.20318, 38.63824, etc.). Tried to re-format the cells to "Number" - doesn't work either.
Code:
Sub searchArray()
Dim myArr() As Variant
Dim i As Integer
myArr = ThisWorkbook.Worksheets("Sheet1").Range("G1:G8594").Value
For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i)
Next i
End Sub
I need to declare a two dimensional array that will return the Row and the cell's string value. My only solution is to return the row as a string like this.
Public GlobalArray(2, 100) As String
How to get more control than this? Maybe I should just declare it as a variant but this might open it up for problems later.
i'm trying to develop an array that is populated when a userform is activated. The userform has a command button that when pressed, will cross check the information filled out in the userform with the entries in the array. If there are no matches then the array is ReDim and the new information is added to the array. Once all the entries have been made. The array is then transposed to a sheet titled "Database". My problem is that vba is not allowing me declare a public array.Below is the first part of the code. Which is when the userform is activated.
Private Sub UserForm_Activate()
With Sheets("Resources")
cbZIP.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
cbBED.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
cbBATH.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
cbSTABRV.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
End With
[code]....
How can I assign values from a listbox to a twodimensional array?
and next
do something like search and remove a row and assign the array back to the listbox
I know I can do it directly, but I need the array for something else too
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.
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:......
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).....
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
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...
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.)?
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
View 4 Replies View RelatedI would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
following is code for search in the sheet and give result in other sheet
Private Sub Searchbton_Click() 'Serach button click
'for search
Dim a(), r As Range, res, i As Long, ff As String, rng As Range
Dim title
title = Array("Sr.No", "File#", "Client File#", "Client", "State", "Borrower's Name", " Date Received", "Date & Time File Started", "Title Ordered on", "Type of Search Ordered", "Title Follow up Date", "Title Received on", "File Completed on", "Date & Time E-Mail Sent to Client", "Status", "File Canceled Date", "Remarks", "Done by")
res = Application.WorksheetFunction.Substitute(Me.TextBox1, "-", "")
If Len(res) = 0 Then..........
Problem is when we search for any data and if the recorded data exceeds 307 or more rows, the result will not show the data beyond 307 & more. it take to the Error : Run-time error '13' :Type mismatch error and cursor goto:
.Offset(1).Resize(UBound(a, 2), UBound(a, 1)).Value = Application.Transpose(a)"
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.
Below, I interpret "vArray" as a vertical array, but the output is horizontal when I run a VBA procedure with this line
Sheet5.Cells(1,1).Resize(1, 100).Value = vArray ( The horizontal values copied are correct! )
If I change this "Resize(1, 100)" to "Resize(100,1)" the output is now vertical but the cell values copied are all exactly the same to vArray(1)
How can this line to be modified to be horizontal and its values the right ones?
I have a string which follows this format: "App Alg FMA", "App Pgm FMA", "App Slf FMA" This string can shrink or grow dynamically depending on the number of App*'s selected. How can I split this string and load the App*'s into an Array so the array would contain the following
Array(0) = "App Alg FMA",
Array(1) = "App Pgm FMA",
Array(2) = "App Slf FMA",
I've tried using a Pivot Table, a Match Array V-Look Up formula I found on here (and can't seem to find anymore) that I altered to fit my cell needs, and even implemented RP-Excel.
This is an example of what I have:
Box
101
101
102
102
102
[Code]...
This is an example of what I'm trying to do:
Box
101
102
103
104
[Code]....
Additional challenges:
The actual workbook is over 4,000 rows long with a lot of various other information in neighboring columns.
The dates represent each time a specific box was filled. Some boxes only filled once, some up to 14 times. I only want up to the first five fills.
Able to try any of the following, or combinations of: Formulas, Pivot Tables, VBA, RP Excel