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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
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
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
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
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 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
Usually Array Formula But Function
I'm just getting into functions, so please bear with me on this as I start to understand how they really work. I do have the VBA and Macros for MS Excel book which has been a great help! Normally on this I would use a simple array formula to gather a total calculation from a timesheet, but in this case there can be numerous timesheets to add this up. Hours would be recorded on a sheet with both a client and a task selected. I also have a summary sheet to show clients against tasks... adding all the hours from each timesheet. If it were from a single Timesheet (possibly creating a single one of these for all sheets is an option?) I would be able to use the following array formula: =SUM(IF((Timesheet1!$F$8:$F$22=C$5)*(Timesheet1!$G$8:$G$22=$A9),Timesheet1!$D$8:$D$22)) Timesheet1 Column D = hours Timesheet1 Column F = clients Timesheet1 Column G = tasks Row 5 (starting Column C) = clients in summary sheet (across top) Column A (starting Row 9) = tasks in summary sheet (down side)...........
View Replies!
View Related
Function That Returns The First Value In An Array
I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells. In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.
View Replies!
View Related
MEDIAN() As Array Function?
Is it possible to submit the MEDIAN() function as an array (ctrl + shift + enter)? I.e., I would like to submit a function similar to this SUM() function: {=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))} However, when I submit {=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))} it does not give me the intended result.
View Replies!
View Related
Using Large Function In An Array
Column C contains the names of real-estate brokers. Column E contains the colors of the homes for sale. Column Q contains the values of the homes. I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21. I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View Replies!
View Related
Function To Write An Array
I have the following assignment to do, problem is I'm new to arrays and functions in VBA and nothing is working for me, i get errors no matter what i try, (for example i don't know how to feed the array to the function, and it won't let me write a range to an array) 5.A) Write a function that takes a 2D array of doubles, a single cell range, an optional cap and an optional floor. B)Write the array on to the sheet with the single cell range parameter being the location of the top left of the array on the sheet. When writing the array to the sheet, for each value in the array where it is below the floor put the floor on the sheet, where the value is above the cap put the cap value on the sheet, otherwise display the original value. The input array should not be modified. All code should be what you consider production quality. Dim topleft As Range array1 = Range("B8:C18").Value 'array1(1, 1) = 2 'array1(2, 1) = 3 'array1(1, 2) = 6 'array1(1, 3) = 9 'array1(2, 2) = 4 'array1(2, 3) = 5
View Replies!
View Related
Error On Function Applied To Array
I have the following code (below) which calls a function to return the sum of values in a specific part of the array. I haven't set the function up correctly as it is causing an object not defined error(424), but I can't see where and have been going round the houses changing it. I'm wondering if it's my confusion over when to use By Val? Sub getDataIntoArray() Dim arrDataTot() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next ReDim arrDataTot(1 To 1)
View Replies!
View Related
Rank Function With Array Inputs
why this returns a #Value! error? {=RANK(MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)),IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1),1)} I just used the even row selector as an example. MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)) returns 203 and IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0} when you press F9. If I change the range to exclude the non-numeric value I get the same error (it shouldn't matter according to the help file as non-numeric values are ignored).
View Replies!
View Related
Function To Calculate IRR Of An Array
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part. Option Base 1 Dim i As Integer Dim j As Integer Dim counter As Integer Sub General() 'Makes general calculations Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý Dim FIRR(49) As Variant 'Yearly FIRR Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet End Sub
View Replies!
View Related
Solver With The Frequency Array Function
In the attached file I have an example. I've used the Frequency array function to get the distribution of results from some sample result data (50 results). I want to use Solver to change the Bin values to what they would need to be to provide a desired distribution, such as in the example file. I've tried setting the target cell at either the sum of the frequency results or sum of their percentage distribution (100%), and set contraints either frequency results or percentage distribution respectively, along with Bin contraints being consectutively larger (requirement of the Frequency function). Alas, I can't get a solution, only the response of "Solver could not find a feasible solution".
View Replies!
View Related
Match Function With Dynamic Array
I am trying to replicate the following code using Cells references so that the row can be made a variable. I basically want to make the search array smaller with each loop. LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250"), 0) The above works, the following two variations don't: LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range(Cells(ArrayStart, 2), Cells(250,2), 0) LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250").Item(ArrayStart, 2), 0)
View Replies!
View Related
User Defined Function In Array Formula
I have dates & times in column A1:A20. In B1:B20 I have the corresponding temperatures for each date. I have set up the following dynamic ranges to refer to these ranges. DateRange refers to A1:A20 TempRange refers to B1:B20 I have also made a user defined function that will determine if a date/time is between two times. Eg., If 21/05/06 07:30 is between "07:00" and "17:00". This function isn't concerned with the date, just if the time falls between the start and end times. Function BetweenTimes(dDate As String, dStartTime As String, dEndTime As String) As Boolean dDate = CDate(dDate) dStartTime = CDate(dStartTime) dEndTime = CDate(dEndTime) BetweenTimes = False 'If the end time is before the start time, see if date/time falls between start and end..........
View Replies!
View Related
INDEX SMALL ROW Array Function
Please see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.
View Replies!
View Related
Aggregate Function On Vlookup Array Not Working
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate. Cells A1 to B5 contain a lookup table: table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;} .tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;} LetterNumber D4 C3 B2 A1 Cells A7 to C8 contain the input values: table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;} .tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................
View Replies!
View Related
VBA Sort String Array Function
I have a string that I need to sort. Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well? Function Alphabetize(ByVal sText As String) As String Dim sWords() As String, sTemp As String Dim i As Long, j As Long, n As Long '-- clean up text For i = 1 To Len(sText) Select Case Mid$(sText, i, 1) Case " ", "a" To "z", "A" To "Z" Case Else: Mid$(sText, i, 1) = " " End Select Next '-- remove leading and trailing spaces....................
View Replies!
View Related
Array To Match Several Results Within SUMPRODUCT Function
I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities... Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True". The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula: TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008". I'm trying to extend it to something like: TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help). Here's an excerpt of the formula that works well for other queries on my worksheet: =SUMPRODUCT(--(TEXT('Current update analysis'!$O$2:OFFSET('Current update analysis'!$O$2;(COUNTA('Current update analysis'!$B:$B)+20);"mm.yyyy")=TEXT({H$7;I$7;J$7};"mm.yyyy")); --('Current update analysis'!$k$2:OFFSET('Current update analysis'!$k$2;(COUNTA('Current update analysis'!$B:$B)+20)="ok")) how I could/should structure the formula for it to work?
View Replies!
View Related
VBA Array Function For Polynomial Coefficients
I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula: {POLFIT(Xa, Ya, N)} Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit. It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells. I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is Function POLFIT(Xa, Ya, N As Integer) As Variant Various means I have tried to count POLFIT do not return the correct value.
View Replies!
View Related
Function To List Data Pulled From 2-D Array
I'm not sure if this is possible but I am trying to find a function that will organize data that will be in a set 2-dimensional array (ex. 6 x 8) into a single list column. The trick is to omit any blanks that may be in the array so that the final list contains no blanks. This is for a template; the data will constantly be changing so it's not a one time project where I can just sort the data to omit the blanks. In the example I am trying to automatically have the 'Initial Group' organized to look like the 'Final List.'
View Replies!
View Related
Index/Match Function Inside An Array
Solution for all: {=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$16,$F$34:$F$40,INDEX(Sheet1!$B$2:$K$16,0,MATCH(T$10,Sheet1!$B$1:$K $1,0))),U34:U40)} Say I have an Excel workbook with two sheets: Employee Hours and Employee Wages. On the Hours sheet I have 31 columns (Jan1 - Jan31) with inputs for the number of hours worked by each employee by day. On the Wages sheet I have listed the same 31 columns with their wages each day (I do this to factor in wage increases that occur fairly often). My goal is to use an INDEX/MATCH function to calculate the total daily compensation for each day on the Wages sheet by multiplying hours*wages for all employees that day. Here's the code I'm using:
View Replies!
View Related
Make A Column Array Variable Within A Function
How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).
View Replies!
View Related
Function To Return Single Dimensioned Array
I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function. Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant Dim vHolder As Variant Dim vArray As Variant Dim lElementCount As Long lElementCount = 0 For Each vHolder In dataToConvert lElementCount = lElementCount + 1 Redim vArray(1 To lElementCount) vArray(lElementCount) = vHolder Next vHolder CreateSingleDimensionArray = vArray End Function
View Replies!
View Related
Using Array Constants In User-defined In Vba Function
I have a function in VBA of the type. Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error. I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).
View Replies!
View Related
Autoselect Range/Row Reference For Linest Array Function
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 = =LINEST(K8:K50,M8:N50,TRUE,TRUE) . Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE) Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0! I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
View Replies!
View Related
Large SumProduct Array -- Original Title:Loop Function
could provide me a loop function in VBA for these calcultions. Cell A1*E1 + B1*F1 + C1*G1 Cell A2*E1 + B2*F1 + C2*G1 ......till CellA100*E1 + B100*F1 + C100 * G1 These are the calculations for E1-G1. The process must be repeated till E50-G50 So the second step is: Cell A1*E2 + B1*F2 + C1*G2 Cell A2*E2 + B2*F2 + C2*G2 ......till CellA100*E2 + B100*F2 + C100 * G2
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
ARRAY.JOIN Function: Eliminate Any Blank Cells From The Final Column
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of all the values from a 10 x 10 array that does not have values in every cell. (I would also like to eliminate any blank cells from the final column.) I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values. how to properly execute that function. One earlier comment was that I was not specifying criteria. I'm not sure from the description and help section of "morefunc" as to what those criteria specs are, other than specifying the whole array. I am attaching a test file with the 10x10 grid & some missing values in A1:J10. Cell L1 has the ARRAY.JOIN function with the entire grid specified, but the output is only the first cell of the grid.
View Replies!
View Related
|
TRACKER
