I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows.
Here is my problem:
I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below.
Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row.
In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3).
Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
I've got a database with 8 columns and many records. The task is to find the value in column 3 for the smallest number in column 7 with additional criterias for columns 4 & 5.
I've used the DMIN function for finding the smallest number, but I don't know how to show the corresponding value in column 3. I think I should only use DB functions. (I've checked and there is exactly one line with the set of criterias above, so there should be no errors.)
Is there any way I can specify to DGET that I want to get the minimum of a column but based on the other criterias? Or embed DB functions into one another?
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code: Public Sub CSVparser(file As String) Dim Lines As Variant Dim j As Integer Lines = Split(file, vbLf) ReDim breadCrumbs(UBound(Lines)) As Variant For i = 1 to UBound(Lines) - 1 breadCrumbs(i) = Split(Lines(i), ",") Next i End Sub
I'm using the formula: =IF(G28>VLOOKUP(D28,MileageLookup,2,FALSE),G28-VLOOKUP(D28,MileageLookup,2,FALSE),"0") in my "Overage" column[H28] to look up weekly mileage limits for employees and calculate by how much(if any) the "Actual Mileage"[G28] exceeds this limit. If there is no overage for the employee that week, I've asked it to return a 0 so that the next column can use this result to calculate the "Overage Charge"[I28] which based on a rate in the MileageLookup table. Both formulae are working properly.
The problem comes when I try to use the DAVERAGE and DMIN functions on the "Overage" column. Excel doesn't seem to recognize that the 0's are actually legitimate zero values and not just some sort of place holder even though it calculates the "overage charge" properly and recognizes the 0's.
I'm new to the DMIN function and I'm having a problem.
Cell A2: Date (rest of column is date format) (heading for the column) Cell A2: Credit (rest of column is acctg. format) (heading for the column) Cell A2: Debit (rest of column is acctg. format) (heading for the column) Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column) Column E: Notes (rest of column is generic text) (heading for the column)
Cell K1: Date (the text "Date") Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?
Cell H2: =DMIN(A2:D1000,"Date",K1:K2)
I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).
I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
| ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.
formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)} where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17 Array2 is a corresponding sequence of numbers 1 2 3 4 5 6
the resulting array should return the numbers 0 0 3 4 0 0 and my answer should be 4 instead my answer is 0
when I break it down and select six cells and use the formula: {=if(Array1+8<20,Array2,0)} my result is: 1 2 3 4 0 0
and another six cells and use the formula: {=if(6-Array1<0,Array2,0)} my result is: 0 0 3 4 5 6
when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)} my result is: 4
when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)} my result is: 6
I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.
I have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.
I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.
Sub test()
Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer
The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?
I have an array that will open specific workbooks. But Now I need to append a Case Number to the beginning of the file, and I don't know how to cycle through two Arrays. I have been setting it up as:
Code: Dim Report Dim Reports Dim WB AS Workbook Dim WS AS Worksheet
For Each Report in Reports Then I open the workbooks and print them. Next Report
Now I want to add in a 2nd Array, that will Append the 1st item in CRN to Reports, then the 2nd item in CRN to 2nd item in Reports. So it would look something like this.
Code: Dim Report Dim Reports Dim CRN Dim CRNS Dim WB AS Workbook Dim WS AS Worksheet
I have two arrays of the same size and shape. Question: is there a more straight forward way to add up all the corresponding elements in both arrays without looping through each pair of numbers one at a time?
Does anyone happen to know of a list of formulas in Excel that can be used as Array formulas?
I am trying to get a better grasp of what happens with array formulas in Excel - for instance MATCH seems to be a good candidate to hold an array of values when confirmed with CSE, but INDEX seems like a formula that wouldn't itself hold an array of values (although it might be able to lookup the corresponding cells of two arrays with corresponding rows and columns).
Below is my attempt to understand working with arrays of values.
Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!
Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:
I need to find the possible combinations for several arrays consisting of binary data. I shall give a simplified example with three arrays and four fields. Lets assume the three starting arrays look as follows:
1 0 0 0 1 0 0 0 1 0 0 0
Each row may contain one field = "1" and all other fields = "0". So the next possible combinations may be:
1 0 0 0 1 0 0 0 0 1 0 0
1 0 0 0 1 0 0 0 0 0 1 0
etc...until:
0 0 0 1 0 0 0 1 0 0 0 1
Assuming k = number of fields and c = number of arrays the possible combinations are k ^ c = 4 ^ 3 = 64. I am looking for vba coding that allows to cycle through all possible combinations and displays them in an excel spreadsheet. Ideally, the code should work for any k and c. Could anyone please give me any pointers as to how to tackle this problem?
p.s. in essence I'm looking for the tabular representation of a tree plan with four branches to commence with, a total of 16 decision nodes, and two possible outcomes (1 and 0)
Well I find myself going round in circles so I thought I'd just ask hopefully solve things quicker. I'm struggling with arrays in VBA, at some point I could use them but now it seems I've forgotten everything and even reading some tips on-line doesn't seem to be helping at this point.
I'd like to be able to use a dynamic 2-dimensional array to store values. I'm reading these in from a worksheet under certain conditions (depending on a value in another column) and that part works. I know I'll always have 2 columns but I don't know the amount of rows. I'm calling a Sub that does an if-test and copies the valeus into the array, then I'd like to "reDim" the array 1 row larger for the next pass.
Column1 Column2 Text1 Value1 ... Text2 Value2 ... Text3 Value3 ... To get an array like so:
Arrayindex(i,1) Arrayindex(i,2) Text1 Value1 Text2 Value2 Text3 Value3 Depending on how I make initial declarations I either end up with an "subscript out of range" or "array already dimensioned" error. So I'm apparently not getting the right syntax in the dim or redim?
I am trying to loop through the values of an array that was populated from a range. Essentially I'm trying to manipulate a pivottable depending upon what a person selected in a control form. Below is my Dim userWeekArray As Variant userWeekArray = Sheets("Valid Values").Range("A20:A27") For w = 1 To 8 If .PivotItems(w).Name = userWeekArray(w) Then .PivotItems(w).Visible = True Else .PivotItems(w).Visible = False End If Next
I get an error message stating that Subscript is Out of Range. It seems Excel is assuming that userWeekArray is a Range object when I want it to be an Array. How can I populate an array with elements from a range in a worksheet so that I can then easily loop through the array to examine the elements?
I'm trying to do a loop through four arrays named Asc, Bsc, Csc and Dsc. I'm using the following code to do this but it comes up with a Type Mismatch error on the penultimate line. Please could someone explain the problem? I'm getting a bit confused as to whether I'm mixing up different types of variables, and not sure if CVar is the appropriate function.
For ch = 65 To 68 Sc = CVar(Chr(ch) & "sc") For r = 1 To 3 If Sc(r, 1) = "" Then 'ERROR HERE etc...
Option Base 1 Dim Covariance(1 To 5, 1 To 5) As Double Dim Mean(1 To 5) As Double Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer Dim m As Integer
I want to have a dynamic range that will increment and return a value to a variant for some comparisons. Here is what i have
For i = 0 To 50 If [Q + i].Value <> "valid" Then Goto GotLicenses Else varHostNumber(i) = [C + i].Value MsgBox varHostNumber(i), vbOKOnly End If GotLicenses: Next i
I want my if statment to use the variable "i" as part of the range for the comparison. Right now the msgbox is just to see what is happening, but will later become a different if statment that will blank a cell's value if it doesn't match the array variables.
I have imported realtime currency prices into a spreadsheet using DDE links, if I have 5 rows showing the last 5 days prices the data is displayed with the most recent days record at the bottom of the table however I would like the most recent records to be displayed at the top.
ACTUAL RESULT 11/01/20071.9536 12/01/20071.9607 15/01/20071.9668 16/01/20071.9705 17/01/20071.9666
DESIRED RESULT 17/01/20071.9666 16/01/20071.9705 15/01/20071.9668 12/01/20071.9607 11/01/20071.9536
I have tried sorting the data by the date column, however because it is an array it will not allow this. I know I could paste link each row into another area of the spreadsheet so as to invert the data and maintain the DDE links; this is fine if I only have a few rows but if there are hundreds then it is a bit time consuming. I was wondering if there was a better method of inverting the data so that the most recent records appear at the top of the table.
