Dynamic Range Performance

Nov 10, 2006

I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.

Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.

I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?

View 3 Replies


ADVERTISEMENT

Poor Performance With Range Names

Aug 24, 2006

why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")

Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()

Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False

For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter

View 7 Replies View Related

Poor Performance Finding/Setting Range

Aug 23, 2006

Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.

1)finding which sheet that the range name resides see getRangeAddress() and

2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)

The CSV file looks like this: <range name>,<data value>
e.g. DPA_1001,99090

Performanace actually seem to slow as the macro runs.

Public Sub readDatapoints()
'Macro readDataPoints
'This macro will read in the a comma seperated value (CSV) file of datapoints.

Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim dprecord
Dim oldStatusBar As Boolean
delimit = ","

View 9 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Maximize Performance Of Array In VBA

Jun 20, 2007

I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want


For i = 1 To N
For j = 1 To M
A(i,j) = Cells(j, i).Value
Next j
Next i

View 9 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View 9 Replies View Related

Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies View Related

Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

View 3 Replies View Related

Formula To Calculate Staff Performance

Nov 6, 2008

I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this

1 - Yes
0 - No
left blank - n/a

B3 counts the number of entries in the column
B2 counts the number of 1's in the column
B28 = B2/B3

Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?

View 6 Replies View Related

Formula To Calculate A Fee Based On Performance

Sep 8, 2009

I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:

KEYPerformance
2010,000
2120,000
2230,000
2340,000

I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999

So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

View 13 Replies View Related

VBA Autoshape Arrows To Show Performance

Oct 2, 2008

Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.

Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).

The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.

Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?

View 7 Replies View Related

Visual Basic Compiler - Odd Performance

Nov 23, 2009

I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.

View 4 Replies View Related

Excel To Analyse Weightlifting Performance?

Jan 17, 2013

I have thousands of rows of data sorted chronologically in a flat list. What I'm looking to do is have a pivot table or formula which picks out the highest value for a given person. Sound simple?

Okay,
- column A: date
- column B: name
- column C: exercise weight
- column D: exercise rep's
- column E: exercise predicted max (essentially, weight x reps)

Now, I have a pivot table which picks out each persons highest "exercise predicted max" (right click on table, field settings, MAX) but I want to know the actual weight they've lifted and how many reps they've done with it.

So basically, I want a simple table which shows me the date, persons name, heaviest weight they've lifted and how many reps they did with it. I've used the CONCATENATE function to group the "exercise weight" and "exercise rep's" in to one cell but the pivot doesn't return any values (all cells in pivot = 0).

View 1 Replies View Related

Array Formula- Performance Rating

Jun 26, 2007

I have a list of employees who each have a performance rating (very top, consistent, etc). Each employee is also either above the midpoint of a salary range or below (which is represented by a separate column with 'Yes' for above and 'No' for below).

What I am trying to do is apply a separate set of %'s for each group of employees. So, for the employees who are below, I want to return 10% and 8% for each respective rating, but for those employees who are above, I want to return 8% and 6%. (for example).

View 9 Replies View Related

Macro Performance - Speed Tuning

Jun 4, 2008

I run a simple macro loop to clean some data across nine columns. The purpose is to collapse the data in the columns so that column 1 has the first value found in that row, for the set of columns. For instance, if columns 1-4 are empty, it deletes / shifts everything left until the first column is not empty. Then it goes to the next row and repeats. Data can range from a few rows up to 6000.

[I have a period in the data as the cell content to evaluate]

Sub A_Rollup_collapse()
StartT = Now

Dim Col As Integer
Col = Range("IV1").End(xlToLeft).Column - 9
LastR = Range("A60000").End(xlUp).Row

Application.ScreenUpdating = False
For R = 2 To LastR
Do While Cells(R, Col) = "."
Cells(R, Col).Delete Shift:=xlShiftToLeft
Loop
Next R
EndT = Now
Application.ScreenUpdating = True

msg = "Done. Duration: " & Format(EndT - StartT, "h:mm:ss")
MsgBox (msg)

this macro (B) runs after another macro (A) that populates the nine columns with data using vlookups. Macro (A) It builds out a chain of information from col 2 to col 9, converts to values etc. Nothing odd.

When macro (B) is called right after running macro (A), it can take about one minute for 500 rows of data.

When I save and close the workbook, reopen it and run macro (B), it only takes one second.

When I insert a ThisWorkbook.Save between the two call statments, macro (B) still takes over a minute.

A minute is not too bad but when I'm dealing with thousands of rows, the difference is more like 30 seconds vs. 9 minutes which is a problem.

View 9 Replies View Related

Sheet That Records Performance On Individuals

May 1, 2009

I've a sheet that records performance on individuals, what it does is collate the performance daily on certain things.

Most are simple plus/minus one and i have control buttons that do this,what it does is: On click it adds one to a different sheet, or if to remove it minuses one, and there is a total box to sum these figures.

But there is also some provision to add collected money value, and what i would like to do is have a function that deletes the last figure added to the financial transactions
This is the code
If Target.Address = "$A$6" Then Sheets(2).Range("A65536").End(xlUp).Offset(1).Value = Target.Value

So for example
in A6 £10.50 is added, but i can't think how i can provide a service that will delete the last figure in Sheet 2 range A, as this will be constantly changing.

View 9 Replies View Related

Performance Drop With Multiple Webquerys

Aug 19, 2006

I have built a fairly complex app in excel which takes 50 webqueries and makes a table out of specific data. It generally takes ~34 seconds to run through the webqueries, and when its done, it starts all over and runs the 50 queries again on 30 second delays. My problem is that for about 5-10 of these cycles, everything works fine, but after that, the time per set of queries starts going up...in the range of 2:00 or more for the same 50 webqueries that originally took ~34 seconds.

Without posting each of the userforms and modules, is there a general way to clear out the memory or see whats bogging down the process or anything between sets of queries to maintain performance? the webquery itself is a

With ActiveSheet.QueryTables.Add(Connection:= "string" & variable

statement that gets called each of the 50 times. The only thing I can think of is that somehow variables or some other procedure isnt clearing after each of the sets of 50 queries runs. Is there any way to check that or make sure it is cleared before it begins the 50 query set again? I noticed this mostly by watching my "physical memory avaliable" drop a small amount each time the set ran, and I had to quit excel before it would return to its original amount.

View 6 Replies View Related

Convert Dynamic Range To Static Named Range

Sep 13, 2007

My searches have not produced anything that I could apply to this situation.

I'm trying to write VBA that would:

1. Search a Workbook for Dynamic Ranges.

2. When a Dynamic Range is found the code would:

A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"

3. Save Changes.

4. Close File.

My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.

View 9 Replies View Related

How To Show Monthly Performance Based On Products

Mar 4, 2014

What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).

View 6 Replies View Related

Slow Performance Entering Values On Spreadsheet

Jul 3, 2014

I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.

The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.

I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.

View 4 Replies View Related

Create KPI System In Excel To Measure Performance Against Target

Jan 3, 2014

Looking to create a system to measure KPI (key performance indicators). I need to include a weekly target and then measure performance for that week against the target. I want to create a form to make data entry simple, how best it would be to implement using excel. Would I have 2 tables, one for targets and one for performance and then use look ups, [URL]

View 3 Replies View Related

VLOOKUP Performance Is Taking Very Long Time To Recalculate

Jan 11, 2010

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

View 3 Replies View Related

Data For Stocks - Analysis For Worst 12 Mths Performance Etc

Apr 16, 2006

I have a data base for a stock.

It goes like this:

Date Price
Date1 Price1
Date2 Price2

How can I do an analysis of the:

Worst 12mths performance (like how much is the maximum a stock drop in any rolling 12mths period)

best 12 mths performance

View 4 Replies View Related

Dynamic Range 2003: Run Calculations On This Range

Nov 5, 2009

I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.

View 4 Replies View Related

Dynamic Range Used Named Cell Range

Jul 7, 2014

Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

View 1 Replies View Related

Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

View 3 Replies View Related

Comparing Male And Female Salaries Based On Performance And Gender

Jun 19, 2014

I have a list of employees split out into gender and performance rating. I want to graph this to compare male to female salaries and performance. Data attached.

View 3 Replies View Related

Does SSD Hard Disk Or A Faster Chip Improve Excel Performance

Apr 26, 2013

If I want to improve my excel performance like ability to open multiple excel and sheets to perform calculation, should I change to a SSD or a faster intel chip? My current PC sometimes give me a black screen for my excel sheet or is unable to save certain sheets when I open too many sheets.

View 3 Replies View Related

Dynamic Name Range

Jun 19, 2009

I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"

=OFFSET(Storage_Team2!$A$1,0,0,MATCH("***",Storage_Team2!$A:$A,-1),37)

What would be the best to create the range according to the specific words in a column?

View 9 Replies View Related

Dynamic Range Within VBA

Jul 9, 2006

i am coloring cells:

Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5

i want a dynamic range, so something that would look like this as an excel function:

=OFFSET('MySheet'!$A$1,0,0, COUNTA('MySheet'!$A:$A),6)

i want the equivalent of this programmed (hard coded) into vba

i don't want to reference to a defined name with vba, because the user can delete that.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved