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


ADVERTISEMENT

Performance And Potential Grid (nine Box Grid) - Multiple Values

Oct 24, 2013

I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.

Pasta1.xlsx

View 1 Replies View Related

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 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

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

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

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

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

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

Multiple Drop Downs

Sep 26, 2007

I'm working with 2 drop down lists created through Validation.

I'm trying to make this scenario work.

If A is entered in B1, then the drop down list in C1 would appear in D1.

If B is entered in B1, then the drop down list in C2 would appear in D1.

List C1 has 12 choices.

List C2 has 8 choices.

I'm making a data entry tab so for the sake of simplicity I only want one list to show up at a time depending on the variable A or B

View 9 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

Conditional Drop-Down For Multiple Lines

Jul 29, 2014

I have a list of car-dealers and licence plate numbers. f.e. data in tab 1 Supplier-Cars.jpg

In another tab (let's call this tab 2) I have to make drop-down lists, depending on the value in this drop-down the cell next to it must give the list of cars from this supplier. f.e. if I select in the first line Supplier 1, I get a drop-down list where I can select Car 1 if I select in the second line Supplier 4, I get a drop-down list where I can select Car 4, 6 or 7 etc. Because of the number of suppliers, I can not work with predefined lists where I put all the suppliers one next to the other...

have a drop-down in each line to go look for the cars depending on the supplier I selected ?

View 2 Replies View Related

Creating Multiple Drop Box In Hierarchy

Apr 30, 2009

I did all the search for multiple drop box or list box that can connect to other list boxes but no luck.

It's the same as when you surf with Internet Explorer, you can click "Views" in menu bar, list box appear and then choose "Encoding", another drop box appear, then when you choose "More", then another box appear.

Is it possible to have this sort of hierarchy list box in excel where drop box connect to another drop box?

View 6 Replies View Related

Unhide Multiple Sheets Using Drop Down?

Oct 7, 2011

I am making a workbook that populates most of the data off of a data sheet, but i need it to hide the 10 other sheets until they are needed. I was hopint there would be a way to hide all but the data sheet, then use either a drop down box or if necessary a checklist (NOT my first choice, but i'll do it if i have to) to select the sheets that the tech will need to print or e-mail. Is there a way to do this:

View 1 Replies View Related

Drop Down List For Multiple Columns

Jan 5, 2008

I have the following in a Validation/Data the following tries to reference and array f2:j11

=if(d5="",teammember,index(teammember,match(f2,teammember,0)))

The array 'teammember' refers to cells f2:j11. Formula returns an error message that it can only refer to a single row or column. Is there a formula

View 9 Replies View Related

Drop Down List And Multiple Information

Aug 5, 2008

I created a drop down list of contacts for specific accounts:

Primary
Second
Third
Fourth

When I click on each one, I want in adjacent individual cells their name, address, phone, and email. The results should only show up in the same row as per list choice.

This drop down list applies to 20+ accounts so each contact info corresponds to the accounts.
A E F G H I
So: Account... Contact Name Address Phone Email
(DropList)
And do that for all the accounts.

View 9 Replies View Related

To Select Multiple Items In A Drop Down

Oct 2, 2009

I am new to writing macros and am wondering what is the easiest way to create a drop down and select multiple items separated by comas. The people who are going to input data into this spreadsheet are field people and not technically savvy so need to design something that is just a matter of cliking on the drop down and selecting multiple items and they should show on the same cell separated by commas.

View 9 Replies View Related

How To Get Drop Down List Value To Affect Multiple Cells

Sep 7, 2009

I have a rather simple problem (tough for me!). I am setting up a food log. I would like to have one column with common food items that I have in a drop down list. The list would be about 20+ things. The other columns would have relevant nutritional info such as calories, fat, carbs, etc.

The problem is how do I make the values in the other columns change depending on which food item I pick from the drop down list?

For example, if I choose bread from the list, how would I get the columns for carbs and calories to change accordingly.

The IF function would be too hard because I couldn't nest 20+ IF functions in the formula for each cell. Also, the drop down list would change as my diet changes, possibly reaching 50+ things.

Also, I would like to manually enter a serving size that would alter the nutritional values in each column (for example 0.5*Calories or Carbs).

It seems so simple in my head, but I can't figure out how to get it to work.

View 6 Replies View Related

Multiple Drop Down Menu To Calculate A Percentage

Nov 6, 2008

I have a bunch of cells that use a drop down menu validation from a different set of cells so

Legend (this are in cells $A$7:$A$11)
NA - Not Applicable
0 - Not attempted
1 - Not Yet Competant
2 - Satisfactory
3 - Exceedes Expectations

Other cells have Data Validation using Allow List =$A$7:$A$11 to make the drop down menu option appear. What I would like to do is add a 'hidden number' on cells A8:A11 (A8=0, A9=1, A10=2, A11=3) A7, being NA isn't included in the formula I want to set up.

In cells later on I'd like to make a formula that takes those 'hidden numbers' into account and works out an average. So 'hidden number' / 'potential total' and make the cell display as a percentage, the 'potential total would have to take into consideration that a value has been set, but not use that value, and times the number of values set by three.

View 10 Replies View Related







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