StringConcat (Chip Pearson) Calculation Delay
Oct 20, 2009
I have used Chip's StringConcat code [url]
When I first entered the function code as a formula on the worksheet, the result was instant and correct.
However, if I change the contents of one of the cells in the stringconcat range, the result does not change.
I need to edit (eg F2 then enter) either the cell with the text or the cell with the formula for the result to update.
If I highlight the column where the formula is used and Find / Replace to change "=" for "=", they all update at once (tip I learn't here)
Has anyone experienced this or have any ideas why it may happen?
Notes:
I am using Excel 2000 (work)
I have 6 cols of info (I to N),
These 6 columns contain formulae that basically copies plain text from the same row in Cols A to F. (Since not all the left hand side cells are full, the formula copies down the value above if the cell isblank, or copies the cell value if there istext)
Formula is in Cell G2 and reads
=stringconcat("; ",TRUE,I2:N2)
Code is stored in the Module1
I have 1 header row (Row 1)
I have 16 rows of text (Rows 2 to 17)
Not all rows have text in all 6 columns, but the are no blank cells betweeen data working left to right
I need our IT Admin to install Excel Genie!
View 9 Replies
ADVERTISEMENT
Dec 3, 2009
Is there a way to delay the result of a cell formula by three seconds or so? For example, I need to delay the result value of cell B12 for 3 seconds.
View 9 Replies
View Related
Nov 9, 2006
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
View 2 Replies
View Related
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
Jan 3, 2010
I have a very large spreadsheet, a few columns and rows I attach..
in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.
What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down
see attached sheet
View 5 Replies
View Related
Sep 23, 2007
I can download my data from the web. I can set it to download every so many minutes. But, I have to select run on my vb program to complete calculations and put data in correct format/sheets. I am trying to put a delay/timer in the program so it will refresh on itself and I will not have to keep selecting program run. If I put this in program it appears to refresh in a timely manner however I do not get any data on the sheets ? I have tried the wait routine and the if now = time + routine
View 11 Replies
View Related
Sep 26, 2008
I'm downloading data from a database that takes sometimes up to a minute to download the data. However I have a sum function in the macro that calls the cells that the downloaded data will be in. The problem is the sum function is occuring before the data is downloaded so the sum is always 0. Any way to build in a delay timer?
View 9 Replies
View Related
Jul 13, 2006
is it possible to put a delay of 1 second between these 2 lines?
Private Sub cmdsave_click()
Sheet14. Range("c1000").End(xlUp).Offset(1, 0) = Sheet14.Range("c1000").End(xlUp)
1 second delay here
Sheet14.Range("c1000").End(xlUp).Offset(1, 0) = Sheet14.Range("c1000").End(xlUp)
End Sub
View 7 Replies
View Related
Mar 11, 2009
I want to close a spreadsheet on a timed event. i can manage the timed close but i need to reset the timer if any action occurs. I have attached what i have so far...
View 4 Replies
View Related
Sep 23, 2007
I’m using live data and was wondering if it’s it possible to trigger B1 to change its value from ‘no’ to ‘yes’ 20 seconds after cell A1 changes its value from 0 to 1?
View 9 Replies
View Related
Feb 13, 2008
If for example:
I place number 7 in cell A2, A3, A12 and A22. I want to know the delay this particular number has. Looking at the example placed before the pattern should be the following (A2) 0, (A2-A3) 1, (A3-A12) 9, (A12-A22) 10. After A22 there's is no more number 7 therefore the delay in the example is of 3 i want this to change automatically until the next 7 appears. I've given an example, from C1 to P1 i have written 14 numbers i would like to know the delay of all these numbers.....
View 9 Replies
View Related
May 23, 2007
I don't know any way ask this question without attaching an example. But the problem itself is pretty easy to explain. On the attached worksheet, whenever anyone makes a change to the information in column C (labeled as Act. Chicks) this triggers a worksheet change that makes that cell have a red font. It also triggers a routine (in a standard module) that updates textboxes that continually display a running total.
Here is the problem: After the information is entered, and the routines have finished executing, there is an incredibly annoying delay of about three seconds. That is, it takes three seconds before you can even select a different cell. This is very difficult for the user to deal with, especially when she has a great deal of information that she would like to enter very quickly. What is strange to me is that if I run the routine for the Worksheet Change within the VBE, I get the delay after the last line of the code (in the standard module) has been executed. In other words, I can't see any code that is running during this long "pause." What is the computer doing during this time, and what can I do to fix it?
View 7 Replies
View Related
Feb 24, 2014
Trying to find a way to close outlook from excel workbook close. If outlook is open then delay excel workbook 45 seconds from closing.If outlook is not open then close excel normal. Here is what I've tried with no luck
[Code] .....
View 3 Replies
View Related
Jan 15, 2009
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.3"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.4"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.5"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.6"
etc . . .
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this??
Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
View 4 Replies
View Related
Mar 14, 2013
I am trying to see if it is possible to create an intentional delay when executing a macro. Basically I would like to pause the macro for 5 seconds and then continue with the rest of the code.
Is such a thing possible with Excel?
View 2 Replies
View Related
Oct 22, 2009
Workbook Name Changes – But Workbook Does Not For Five Seconds?? MS Office Excel 2003 / SP3 Installed. I have noticed an odd occurrence from time to time while switching between workbooks. When I switch from one workbook to another, (typically via the “Window” pull down – but the same result happens when switching workbooks on the tabs at the bottom of the screen), the “switched to” workbook name will instantly appear at the top of the application, but the actual switch to the selected workbook is delayed for several seconds, (five seconds or so).
While not a major issue, it is annoying. I suspect that it is a memory allocation issue, (I have a Duo CPU System / XP Pro / 2 GB RAM), and with that in mind I have both restated MS Excel and also performed a complete re-boot. While either option does (seem) to result in the issue being (temporally) removed from my machine, it does return. I may go for several days without this issue occurring, and then it appears for no known reason
View 2 Replies
View Related
Oct 13, 2006
I have created a macro that goes to a report application. The report application runs a script that extracts certain parts of various reports (the script asks for user input to pull the various reports) and exports the data to a spreadsheet. The script gets one report, writes the data to a spreadsheet, gets the next report, appends the data to the spreadsheet, etc. The macro then moves and formats the data in a customized way to be put to second spreadsheet. My problem is that the macro tries to format the data before the script has completed - how can I stop the macro from running until the script has finished? The script varies in run time - anywhere from 1 to 5 minutes.
View 2 Replies
View Related
Apr 17, 2008
been checking out 1/2 a second wait instead of a 1 sec wait
and would like to know it you can do this
timer thing
Dim x As Single
x = Timer
While Timer - x < 0.5
Wend
more than once
it works the first time and then it sort of skips it
or is there ANOTHER way of having a delay of less than a second?
View 6 Replies
View Related
Jul 1, 2014
I have code that generates random numbers 1 - 90, what I would like is when the command button is pressed the numbers are generated every 5 seconds, I have attached the workbook.
View 5 Replies
View Related
Jun 16, 2009
I need a code which will take a lump of data, B2:AW366 and cycle through the data in a single cell (A1)
So to clarify Cell A1 should cycle through:
B2
C2
D2
E2
...
...
...
AU2
AV2
AW2
B3
C3
D3
E3
.....
.....
Continue until cell AW366 reached.
View 2 Replies
View Related
Jan 24, 2013
Total amount to pay by D -
2839300284
Description of Customer
Txn Date
Amount Paid
[Code].....
View 1 Replies
View Related
Feb 23, 2007
I have the following code saving a copy of a spreadsheet as a backup every 30 minutes. The problem is the save code is running as soon as the file is opened. I want to save only after the file has been open for 30 minutes. The way it is now if I open the sheet up and something is wron the backup has already been overwritten by the messed up sheet. Will someone please let me know what needs changed to eliminate the inital running of the code?
Sub do_something()
sec = 1800
when = Now + sec / 60 / 60 / 24
Application.OnTime when, "do_something"
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs "S:QUALITYTest Results Spread SheetBackup of Riser MezRiser Mez backup.xls"
Application.DisplayAlerts = True
End Sub
View 9 Replies
View Related
Aug 2, 2014
My software is Excel for Mac 2011 version 14.4.3 (140616).
I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1
I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".
For example using the PV formula I know that if I have $49,019.96 invested at 1%, it will generate $10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield $10,000 per year for 5 years, again exhausting itself completely in the process, with the first $10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.
View 3 Replies
View Related
Mar 31, 2009
Find latest delay Position of each number which is in the game
View 9 Replies
View Related
Jul 9, 2008
Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.
Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.
We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.
Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.
View 9 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Nov 19, 2008
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
View 2 Replies
View Related
Jul 19, 2007
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
View 9 Replies
View Related
Oct 12, 2007
I have a series of monthly revenues and want to calculate each month a commission % - but only want this commission calculated up to a defined limit from the previous months and current month and then to stop when the limit is reached.
View 13 Replies
View Related
Jan 30, 2014
A
B
C
D
F
1
Received
Overdue
Completed
Untouched
[code]....
Overdue is received in previous slot and still under dependency.
Formula used for untouched - B2+C2-D2
SLA Target Min - 98% and Max 99%
Algorithm - (Total Number of Allocations completed within 5 business days Plus Type A Exclusions) / Total Number of Allocations received minus Type B Exclusions) * 100
I need a individual SLA achieved % which includes untouched too.
View 1 Replies
View Related
