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 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
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 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
Evaluate One Cell Against An Array
I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs: i.e. Numbers Prefix Country 4476324125 44 UK 3354326544 33 France 9713425432 971 UAE 9143253245 91 India 1343543253 1 USA 4432412412 4924241214 7431242424 So I would need to add a column next to "Numbers" saying to which country each number belongs. My list has a few thousand numbers and a couple of hundred prefixes. I tried with some array formula, but cannot make it work.
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
Mark Existing Matrix Or Array Values
I have this example array: A1 B1 C1 D1 A2 B2 B3 C3 D4 C5 D5 E5 ...along with this example table: .12345 A B C D E In my table, I want to notate every occurence of A=1,A=2,B=1, etc; by placing an "X" in the correct cell. So, the table should look like this: .12345 AXX BXXX CXXX DXXX EX I cannot figure out the formula. I've tried this: =If(VLOOKUP($L6,$I$6:$J$15,2,False)=M$5,"X"," ") ....but it seems to only return the first value found, and not all occurences.
View Replies!
View Related
Average Standard Deviation Of Array Within Matrix
I have tried to find information on this subject, but it seems diifficult to find exactly what I am looking for. I have provided a test example of the operation that I would like to acheive. The first example (Method 1) works well enough but would require defining more detailed functions beyond the scope of average. This would probably be ok, but more importantly, I would like to source particular arrays in this manner for other tasks beyond averages and even standard deviation and am not quite sure how this can be done. In an attempt to describe the situation, I have provided a second example (Method 2). In this example, I am trying to take the average of the jth column array to create an array of averages. I am not sure if I am incorrectly setting up the matrix, if the functions don't perform this way in the matrix, or if this is simply not possible. Sub test() a = 5 b = 5 Redim X(1 To a, 1 To b) As Double 'Assumes there is a matrix of values [a x b] drawn from worksheet For j = 1 To b For i = 1 To a X(i, j) = Cells(i, j) Next Next '----METHOD 1--------------------------------------------- 'I know this way works using average as an example, but... Redim AverageX(1 To a, 1 To b) As Double.......................
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
Return Inverse Matrix Of Large Matrix
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
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
Macro – Two Dimensional Look Up
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.
View Replies!
View Related
Dimensional Weight Formula
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?
View Replies!
View Related
Bi-dimensional Dynamic Range
I've tried the below formula to get a bi-dimensional dynamic range that is defined by the last no blank column and last no blank row, too. =A1:INDEX(A1:D18,MAX(NOT(ISBLANK($A$1:$D$18))*ROW($A$1:$D$18)),MAX(NOT(ISBLANK($A$1:$D$18))*COLUMN($A$1:$D$18))) Does any one have a different approach to get this without using the offset function?
View Replies!
View Related
2-dimensional Date Range Criteria
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.
View Replies!
View Related
Double Lookup From Two Dimensional Table
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.
View Replies!
View Related
Formulas Will Not Evaluate
I cannot figure it out for the life of me. When I type a formula into a cell it will not evaluate, instead it just displays the text of the formula. When I go to evaluate the cell -- Tools | Formula Auditing | Evaluate Formula It tells me that "the cell currently being evaluated contains a constant." What do I need to change so that cells will evalute formulas I put into the?
View Replies!
View Related
Evaluate Function
I cant figure out why this "evaluate" function is not working as I expected. I have number/text from $A$1 to $A$5 to try and learn how to use this function but have not had any luck. I need to learn how to use it properly so I can use a for loop to change multiple check boxes on a user form that I am trying to make.
View Replies!
View Related
NOT Evaluate A Formula
I need a way for tell Excel to NOT evaluate a formula. Basically, I want Excel to leave the value being displayed in the cell alone. The formula in the cell is a function that I have implemented in C++ and registered with Excel through the Register call.
View Replies!
View Related
Isblank :: Evaluate All Cells Once?
Ok, this should be an easy one. I have a formula that one of the functions needs to check if certain cells are blank. For example; I want to check if A2, B2 and C2 are blank. Currently, the only thing that seems to work is,=IF(OR(ISBLANNK(A2),ISBLANK(B2),ISBLANK(C2)),"PASS","FAIL") This is just a piece part of the entire formula and I have to evaluate the data based on multiple criteria. The ISBLANK() portion has to be able to evaluate at least 15 cells. Is there a way to evaluate the cells all at once instead of typing out ISBLANK() over and over. I have tried everything I can think of in regards to syntax.
View Replies!
View Related
Evaluate Digits Within Numbers
Example numbers: 21130 & 21065 I want to check each number if EITHER of the two conditions is true: 1. if the third digit from the right (the hundreth place) is greater than zero; or 2. if the second digit from the right (the tens place) is >=6. If either is true I want to add a particular number to the original number. My example numbers meet questions 1 & 2, respectively.
View Replies!
View Related
Evaluate The Information And Lookup
Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website" I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom. I have attached a test file which should clear things up
View Replies!
View Related
Evaluate Text As Formula
I have 2 sheets (book1.xls and book2.xls) book1 has over 20 sheets and has my primary data. Book2 is for a report. The report I want to only display one sheet at a time, by me typing in which sheet to access. What I want to have: A2 = Sheet Name C2 = ="'C:Excelfiles[book1.xls]"&$A$2&"'!$B$1" C2 should have the value from B1 on 'Sheet Name' from the file book1.xls however, all C2 will display is the text, I cannot get it to actually do the reference. (appears as 'C:Excelfiles[book1.xls]Sheet Name'!$B$1 ) Is there a function to say, evaluate data output from another cell? (less complicated example) A1=1 A2=2 A3=A1+A2 A4=3 (formula to evaluate output of A3)
View Replies!
View Related
2-dimensional Date Range With Dynamic Copy And Paste
Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note. The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating. I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent. What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question. The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report. What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.
View Replies!
View Related
Evaluate() For Loop Less Range Calculations
I'm trying to get my head around using this syntax for EVALUATE() to allow an entire range of values to be changed all at once instead of via loop. This macro works: Sub TruncateValuesInSelection() 'JBeaucaire 2/23/2010 'Select a range and then run the macro to truncate values without looping Dim Cell As Range With Selection.Cells .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))") End With.........
View Replies!
View Related
Formula Evaluate To The Wrong Conclusion
In cell A1 is 13.00, the result of a formula. In cell A2 is the formula =IF(A1>14.00,True,False) The result of this formula is TRUE In cell A3 is the formula =15-A1 The result of this formula is 2 why the formula in cell A2 would evaluate to the wrong conclusion? All cells are formated as numbers.
View Replies!
View Related
Conditional SumProduct To Evaluate Iteratively
I have two tables, one summary sheet which contains a list of 30 projects. The second sheet has the projects broken down by task and status ("on time" or "late"), so it has three columns (project, task and status). I am trying to add up the number of projects within each task that are either "on time" or "late" and put these in two columns, so that the sum of each together will always equal 30. The trouble is that any given task can appear multiple times for a project, and if one instance of that task is late, I want to count the whole project late for that task. Here is a table of what I am trying to do: Project Task Status A1 MU On time A1 MU Late A1 FA On time B2 FA On time B2 MU On time B2 MU On time The desired result of this scenario is that under task "MU" I would show a count of 1 projects on time (B2), and one that is late (A1). For task "FA" I would show a count of 2 projects "on time" (A1 and B2) and none for "late" I think that an array formula is needed where it assigns a 1 to a project and task that is on time, and a 0 if it is not, and then multiplies these figures for each project and adds them up.
View Replies!
View Related
Force If Statement To Evaluate Another Formula Value
I am trying to use an If statement and wanting to evaluate another formula. The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6. Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
View Replies!
View Related
|
TRACKER
