Calculates Faster Than IF

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


ADVERTISEMENT

VBA Calculates Sum If From CSV Workbook

May 2, 2014

Sample stock system LOADTESTING.xlsm has ten products. "LoadTesting" shows the opening stock for each. CSV file "Van Sales" has the Stock Number in Col M and the sales figure in Col N. I want the code to Open a CSV file "Van Sales" (which for some reason the site would not upload) Establish the sum total sold for each product (VBA equivalent of SUMIF) Deduct that sum for each item from the opening numbers on "Van Sales" (e.g. Product 001 started with 156 on the van, 92 were sold, so the "revised" van figure should be 64).

Can't get the code to link to the right "ranges" from the Van Sales workbook.

(You will have to change the file path calling the Van Sales file, but if you do get it to work,

VB:
Option Explicit
Sub GETSALE()
Dim Dest As Range, Dest1 As Range, Dest2 As Range, Dest3 As Range, oCell As Range, varResult As Variant

[Code].....

View 4 Replies View Related

IF Function Only Calculates First Value?

Sep 9, 2013

I have written an IF formula that only clacluates the first IF values in the formula and I dont know why.

Here is my formula: =IF(I2999;"1000";IF(I2>1999;"2000";IF(I2>2999;"3000";IF(I2>3999;"4000")))))

So if in I2 I have the value 800 it correctly returns

View 7 Replies View Related

Faster Than For Each

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

I Need A Formula That Calculates Sum Of Row Widths

Oct 27, 2007

I need a formula to calculate the sum of a range of rows.

Example:

If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.

Any idea?

View 9 Replies View Related

Faster (non-loop?) Way Of Doing This

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

Modify Formula That Calculates Time?

Jun 6, 2014

I have the following formula:

=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")

It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?

View 13 Replies View Related

RC[-1] Formula: Calculates Monthly Residuals

Nov 20, 2008

I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula

=RC[-1]+(RC[-1]*2)+(RC[-1]*3)+(RC[-1]*4)+(RC[-1]*5)+(RC[-1]*6)+(RC[-1]*7)+(RC[-1]*8)+(RC[-1]*9)+(RC[-1]*10)+(RC[-1]*11)+(RC[-1]*12)

where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.

View 2 Replies View Related

Query- Worksheet When The Program Calculates

Oct 17, 2007

If I have a worksheet that has a figure when the program calculates, how do I set up in another worksheet so that it tells me how many pages to print (that has information based on the 1st spreadsheet).

View 9 Replies View Related

Formula Calculates Correct Value But Does Not Display

Mar 18, 2008

Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:

Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!

Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!

View 9 Replies View Related

Formula That Calculates A Total Numbers

Oct 3, 2008

Column C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.

How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?

View 9 Replies View Related

If Statement: Calculates 7.25 By The Hours Worked No Matter What Comes Up In F2

Dec 15, 2009

The whole document works but the last one i need. I'll post it all just in case i have an error elsewhere.

Start Time -- Stop Time -- Break -- Total -- Hours Worked -- Average -- Pay
6:00 -------- 3:00 ------- Yes ---- 1600 -- 8.00 ----------- 200 ------ $xx.xx

E2 Formula for time: =IF(C2="Yes",(((A2*24+12)-(B2*24+24))+1),((A2*24+12)-(B2*24+24)))

F2 Formula for average: =D2/E2. and now for the problem one. G2 Formula for pay: =IF(F2<165, E2 * 7.25, IF(F2<180, D2 * 0.07, IF(F2<190, D2*0.08, D2*0.09)))

Just to break that formula down more:
0 - 164 = 7.25 * Hours Worked
165 - 179 = 0.07 * Total
180 - 189 = 0.08 * Total
190+ = 0.09 * Total

it only calculates 7.25 by the hours worked no matter what comes up in F2

View 5 Replies View Related

Macro That Calculates Sum Based On Cell Color?

Mar 28, 2012

The Excel file is in this link

[URL]

If you look at the chart on the left, it breaks down how this whole thing works. It takes the sum of the numbers that fall within each line. I wrote it out on column M so that it's more clear. In column D-L, I filled in random numbers ranging from 4000-700. The numbers in those gray boxes are ignored. This Macro should only apply to Column M and beyond. Is it possible to write a macro that will be able to do this?

View 1 Replies View Related

Excel 2013 :: Formula Disappears Once It Calculates?

Jul 17, 2013

using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?

View 7 Replies View Related

Formula: Calculates % Of Budget Based On Signs

Feb 5, 2007

I am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down.

Actual Plan% Budget
85,60696,85188.4%
70-82185.9%
-1,530-3,786247%
-100100-200%

View 9 Replies View Related

Amend Code To Run Faster

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

Can This Code Be Modified To Run Faster

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

Is There Anything Faster That 'select Case'

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

VBA Running Faster Than Excel

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

Faster Way To Delete Rows

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

Formula That Calculates AVG Number Based On Exact Criteria

Jan 14, 2014

Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?

Please see an example:

Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer

[Code] .....

I guess it has something to do with AVG and IF formulas.

View 2 Replies View Related

Formula That Calculates The Total Rent Amount For Particular Dates

Oct 5, 2009

I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.

View 9 Replies View Related

Faster Way Of Doing VLOOKUPs On Multiple Items?

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

Make Code Faster/better Best Practices

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

Faster Way To Loop For Price Indexing

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

How To Adjust Width Of Columns Faster

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

VBA - Faster Delete All Visible Cells?

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

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

Why Is My Code So SLOW, Anyway To Make It Faster

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

Find Page Breaks Faster

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







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