Faster Calculation Than SumIf Code
Nov 21, 2006
I have the following code performing SumIf and searching roughly 9,000 rows of data. Calculation takes roughly 15 seconds on the high side.
Bonus question from a rookie - This code runs well in Sheet2, but errors out on the SumIf's when placed in a module or userform. What needs to be changed for it to work outside of Sheet2?
Private Sub DistNeg_Click()
Dim BotRow As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").Range("A4:E65536").ClearContents
'Copy current selected Distributor names to sheet2
Worksheets(1).Range("DIST1").Copy Destination:=Worksheets(2).Range("A4")
'Define the bottom row of distributors in this list.
BotRow = Worksheets(1).Range("DIST1").Rows.Count + 3
'Sum for PY, CY and $CHG sales. Create percentage Chg column.
Worksheets(2).Range(Cells(4, 2), Cells(BotRow, 2)).FormulaR1C1 = "=SUMIF(Sheet1!
View 9 Replies
ADVERTISEMENT
Sep 5, 2008
I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)
Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.
View 10 Replies
View Related
May 30, 2009
way to bring more efficiency in this code.
I need to run this code on 100,000 rows every week and this takes hours to finish.
View 14 Replies
View Related
Dec 27, 2008
I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.
View 14 Replies
View Related
May 3, 2009
I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
View 9 Replies
View Related
May 3, 2007
Here is exactly what was described:
A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
This sounds like a great tool for optimization...
View 9 Replies
View Related
May 5, 2014
I need to run the following formula in multiple excel documents so I want to creat a macro for it, creating the needed code for this.
The below formula needs to be inserted in cell B125
=SUMIF(B:B,""IDN"",C:C)+SUMIF(B:B,""MYS"",C:C)+SUMIF(B:B,""PHL"",C:C)
+SUMIF(B:B,""SGP"",C:C)+SUMIF(B:C,""THA"",C:C)+SUMIF(B:B,""VNM"",C:C))"
The below formula needs to be inserted in cell B126
=SUMIF(B:B,""IDN"",D:D)+SUMIF(B:B,""MYS"",D:D)+SUMIF(B:B,""PHL"",D:D)
+SUMIF(B:B,""SGP"",D:D)+SUMIF(B:B,""THA"",D:D)+SUMIF(B:B,""VNM"",D:D))"
The below formula needs to be inserted in cell B127
=SUM(B125/B126)
View 6 Replies
View Related
Oct 22, 2008
I was looking at the message board about what i want but i didnt find it as a whole
What i mean is, that i found the sum of the visible cell only or the sum of a condition
Anyway what i want is:
I hide some rows using a vba code and i want to sum what is left under condition
I have 5 columns , the 2 have the interest.
The column e5:e5000 have values
The column f5:f5000 have specific text --> ("On","Off","Other")
I want ,using VBA, to know, its time i run my the code, the sum of the visble cells under the "On","Off","Other" condition in 3 cells
eg. E1 will have the sum of the "On"
F1 will have the sum of the "Off"
G1 will have the sum of the "Other"
View 9 Replies
View Related
Apr 24, 2008
I need to use a SUMIF formula in a macro. however the columns in the range are variables. I can easily find the columns numbers but I can't find the way to embedded them in the formula. (For example : if the formula is : =SUMIF(A:A,A1,K:K) column "K" may change to to "B").
View 4 Replies
View Related
Oct 9, 2006
Does Excel handle formulas written into the VBA code quicker than just writing out the calculation in VBA?
I have a section where I use the following formulas, sumif, countif and a combo if iserror sumproduct in the VBA code...runs rather slow at this point and was looking at a way to speed things up.
View 9 Replies
View Related
Nov 16, 2013
I have a worksheet (named Deduction Worksheet) that is a running record of all part removals. A removal is denoted by a row entry that contains removal particulars. The first column (Column A) in each row contains a unique number for each part. Column K shows how many part units were removed in that removal:
Column A
... Columns ... B to J ...
Column K
Unq001
various
[Code]....
In the code above 'crng' is the criteria range, 'sValue' the criteria and 'srng' the sum range (which follows the normal SUMIF function within a worksheet). Both the 'crng" and 'sValue' values contain text, / and numbers.
note that other elements on the userform code look at data on other worksheets (not sure if this will effect this).
View 5 Replies
View Related
Jul 8, 2005
I've been trying to modified the formula below into the code you see below.
= SUMIF(G3:G21,">0",G3:G21)-SUMIF(A3:A21,"=",G3:G21)
So how can to modified the SUMIF formula into a code to the finalrow?
Range("J" & FinalRow + 1).Formula = "=SUM(J2:J" & FinalRow & ")"
View 9 Replies
View Related
Aug 14, 2012
I am calculating the employees time and need the time spent to be categorized as follows
Monday to Friday
06:00 to 09:00 is OT
09:00 to 17:00 is BASIC
17:00 to 00:00 is OT
00:00 to 06:00 is PREMIUM
A2 =Date= 31-07-12
B2 =Start Time = 03:00
C2 = End Time = 23:00
D2 = c2-b2 = 20 hours formatted as [h]
there are situations when end date and time is the next day
E2 = 8 hours
F2 = 10 hours
G2 = 3 hours
Saturday
07:00 to midnight is OT
Midnight to 07:00 is premium
Sunday
all the time is premium
View 1 Replies
View Related
Mar 5, 2013
I have an excel spreadsheet that performs a particular calculations using a large set of data. However, I have over 1000 sets of data that need to be feed into this sheet and obtain the output calculation. Obviously I don't want to have to copy and paste in each new set of data to obtain my result as this would take a very long time. I am brand new to VBA, so was wondering if there is a way to write code in VBA that will automatically perform these calculations for one data set, save the output into a cell, and then move to the next data set and so on?
View 4 Replies
View Related
Oct 1, 2006
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click()
Dim Val
Val = "6"
Dim valand As Range
Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0)
valand = Val
Dim A As Range, B, C, d, E, F, G, H, I, J
Set A = ThisWorkbook.Worksheets("Stageing").Range("K2")
Set B = A.Offset(1, 0)
Set C = B.Offset(1, 0)
Set d = C.Offset(1, 0)
Set E = d.Offset(1, 0)
Set F = E.Offset(1, 0)
Set G = F.Offset(1, 0)
Set H = G.Offset(1, 0)
Set I = H.Offset(1, 0)
Set J = I.Offset(1, 0)
TextBox1.Value = A & B & C & d & E & F & G & H & I
End Sub
View 4 Replies
View Related
Dec 10, 2008
I'm trying to write some code that inserts a SUMIF function to a given range of cells:
In simplified form, the code runs as follows: ...
View 9 Replies
View Related
Feb 28, 2013
how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.
View 1 Replies
View Related
Nov 18, 2008
is it possible to choose an option button which will then perform a calculation on a cell reference? I’m trying to deduct a percentage from a total when an option button is highlighted.
View 5 Replies
View Related
Apr 4, 2007
I'm using the following code to populate the columns next to the dynamic named range dataforgraph (varies in length). It works, but the problem is that it takes ages. Is there a more efficient way of doing it. It is important that it only populates the cells next to the varying dataforgraph. The avg1 and stadev1 are the same for each entry (thus constants)
For Each c In Sheet3.Range("dataforgraph")
c.Offset(0, 1) = Sheet3.Range("avg1")
c.Offset(0, 2) = Sheet3.Range("avg1") + Sheet3.Range("stadev1")
c.Offset(0, 3) = Sheet3.Range("avg1") - Sheet3.Range("stadev1")
c.Offset(0, 4) = Sheet3.Range("avg1") + 2 * Sheet3.Range("stadev1")
c.Offset(0, 5) = Sheet3.Range("avg1") - 2 * Sheet3.Range("stadev1")
Next c
View 3 Replies
View Related
Aug 21, 2009
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
View 7 Replies
View Related
Jan 22, 2009
Is there a faster (maybe a non-loop) way of doing this?
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Test1 As Single
Dim Test2 As Single
Dim Test3 As Single
With Sheet2
For Test1 = 33 To 52
If .Range("J" & Test1).Value = "End" Then
.Rows(Test1).Hidden = True
Else: .Rows(Test1).Hidden = False
End If
Next Test1
This causes a bit of a delay (7 seconds or so) when switching to the sheet, which is causing the user to think that excel has locked up.
View 9 Replies
View Related
Sep 1, 2008
Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?
Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)
lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row
For xlrow = 2 To lastrow
arr = Range(Startcell, Endcell).Value
For j = 1 To UBound(arr)
For k = 1 To UBound(arr, 2)
Select Case arr(j, k)
Case A
intcounter1 = 1
Case B .....................
View 9 Replies
View Related
Nov 6, 2009
I have discovered a strange problem with my VBA code. After testing it on several machines, I've found no problems with my workbook until now. After being run on a rather slow Windows Vista laptop, I've found that VBA appears to be running faster than Excel, and consequently it is creating errors as VBA asks Excel to do things before it has finished a previous action. This is causing macros to fail several times in each run, though can be solved simply by pressing 'debug' and then F5 to continue the code. Is there a way to get VBA to run slower to solve this? As I can't think of any other way of doing it? I've certainly not seen it happen on any XP or Win 7 computer!
View 9 Replies
View Related
Nov 11, 2009
My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.
The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.
This is the main routine where I progress down the big lookup table.
'Work down the Price File range from top to bottom
Set RefTableRange = DSWPrices.Range("DSWPriceRange")
RefTableIndex = 1
Application.Calculation = xlCalculationManual
While RefTableIndex < RefTableRange.Rows.Count
RefTableIndex = RefTableIndex + 1
'check if this part number is included in the contract
Call CheckRefTableRow(RefTableRange, RefTableIndex)
Wend.....................................
View 9 Replies
View Related
Nov 9, 2009
Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies
View Related
Dec 10, 2009
I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
View 9 Replies
View Related
Nov 17, 2012
1. Is there a faster way to adjust the size of the columns? I had to use my mouse to stretch the column out. This can be quiet time consuming if there are 8 columns needing to adjust to the same size. ie Col A, B,I, J needs to be the same size.
2. Can Excel determine the best size for the column? Say I need a column with data saying "yes" and "no" and a column for the letter "X" only or do I have to stretch the width of the column myself?
View 1 Replies
View Related
Jul 23, 2013
Any way that the following code could perform faster? Unfortunately, i'm deleting 35,000 rows of data quite frequently from a filtered list. Could be up to 35 columns wide.
Code:
ls = Cells(Rows.Count, 1).End(xlUp).Row
If ls > 1 Then
Range("A2:A" & ls).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
View 1 Replies
View Related
Jul 21, 2007
I need to find row numbers of the horizontal page breaks on a sheet quickly. The only method I have found to do this is by using the HPageBreaks property, which is painfully slow.
Is there another way of doing this that's faster? Or some other manner of using HPageBreaks that improves its speed?
The scenario is that I have a macro which generates a list of items where each item is two rows long. If an item intersects a page break--one row is on one page and the 2nd row is on the next page--I want to insert a row so that the entire item is on the next page.
View 9 Replies
View Related
Feb 18, 2008
I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
View 4 Replies
View Related
