The workbook has two sheets. Sheet1 has numbers in column A going down the sheet and dates in row 2 going across the sheet starting in column B. Sheet2 is similar but the column and rows do not line up with sheet1. I would like the macro to look at the numbers in column A and the dates in row 2 on sheet1 and find the same match of number and date in sheet2 and enter the value from sheet2 into the appropriate cell in sheet1.
I understand it can be done in a formula with index and match but I would prefer a macro.
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.
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
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.
I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great.
The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are:
1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H.
2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H.
3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H.
I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?
Is there an easy way to sort variants? I've searched the internet and it seems that sorting works just with ranges
[Code] .....
I have variant M with 20 rows and 4 columns and want to sort the rows according to the fourth row (from the smallest to the largest). I tried analogical things such as:
[Code] ......
which does not work. I do not want to believe that VBA does not have any internal way (function) to solve variants sorting since it should be quite casual thing..
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?
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
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’?
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table.
For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.
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 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 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
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 -
Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it:
For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely.
What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range.
So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost)
If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria.
Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.
Whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:
X Y 24 0 30 0 10 24 8 38
2L-CVRP, what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution.
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
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).....
