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 RepliesSample 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].....
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
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
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?
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.
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?
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.
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 RelatedExcel 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% !?!
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?
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
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?
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 RelatedI 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%
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.
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.
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 .....................
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 RelatedMy 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.....................................
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.
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.
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 RelatedI 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 RelatedI'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 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?
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
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!
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?
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.