I am using a macro that automatically updates data obtained through a DDE link at 5 second intervals. My code is based off of the article "Automatically Run Macros at a Set Time or Date" found on the Ozgrid FAQs found here: http://www.ozgrid.com/Excel/run-macro-on-time.htm
However, I have this macro execute when I activate a Form Button, not when the workbook opens. So far, this macro works perfectly, and I have no problems. Although, I'd also like to include a Form button to STOP the macro from automatically running if the user chose to do so. How exactly would I go about making this STOP button?
I am trying to do is calculate (sum) results data for time intervals. For example, between 0500-0559hrs then 0600-0659hrs and so on. Data is collected at 1 minute intervals and may have a result of 0 or anywhere up to 30.
Column A has all time data e.g. 0500,0501,0502 etc Column B has sum of instances e.g. 0,1,2,3 etc
I need to compare how many incidents are created within a 15 min interval and populate the sum.
I currently have the create time of the incident in the format below, assume each date below is an incident. I have a column with 96 intervals (24hours) ie 8:00,8:15,8:30,8:45,9:00 etc.... Using a formula how can I say "if create time is between *:00 and *15 count 1 and put the number into the cell and do that for each interval?
A B ------|-------------------| Int Incident 0 | 8:00 | 1/28/2008 8:32 AM 0 | 8:15 | 1/28/2008 8:35 AM 3 | 8:30 | 1/28/2008 8:41 AM 1 | 8:45 | 1/28/2008 8:46 AM
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date b1=source C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
a2 = 8/1/2006 12:20:23 AM b2 = phone a3 = 8/1/2006 12:21:13 AM b3 = email a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish: I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email 12:00AM to 2 3 12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following: =text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.
To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.
Dim x As Integer Dim lastrow As Long Dim timeA As Date Dim timeB As Date Dim timeC As Date Dim timeD As Date Dim timeE As Date
The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:
a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm
b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.
c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.
I'm trying to determine how much time our agents are spending for their lunch/breaks during 30 minute intervals. Range A8:A200 lists the agent's names, Range B8:B200 lists their lunch/break start times, Range C8:C200 lists their lunch/break end times, and Column E lists the times (8:00, 8:30, 9:00, etc.). If an agent starts their break at 10:57 (Column B) and ends at 11:10 (Column C), the value next to 10:30 (Column E) would have 0:03:00 and the value next to 11:00 would have 0:10:00. But would need to sum all the agents which took a break/lunch between 10:30-11:00, and 11:00-11:30.
I have a workbook with that contains macros on a worksheet. When I open the worksheet, I sometimes forget to enable the macros.
1.Sometimes I get the message about macros being in the file that I'm opening and sometimes I don't - I don't understand why it doesn't pop up all the time and be consistent.
2. If I don't get the pop up screen or if I do but forget to enable the macros, I get reminded when I try to run a macro. For example, I have a check box and try to check it but I get the message that the macro has to be enabled. So I enable it, but the macro doesn't run. I have to uncheck and then recheck the box to get it to run.
I have a sheet which needs to run a macro only if certain selected cells are TRUE. The TRUE cell indicates that my colleagues have updated their spread sheet. So then i can go run my macro. But i would like to automate this process. What code would I need to Automate If cell a1 = TRUE run macro? and IF cell A! does not = TRUE do nothing? Bearing in mind this will be all run with Reuters in the background.(which is VERY annoying)
I need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum [url]. Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files,
Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoplesí attention is captured by emotionally relevant cues in the environment.)
For each participant's data, I need an efficient way (macros?) to: (1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)] (2) determine whether enough valid trials remain for further analysis (>39 trials), and if so: (3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type] (4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this: --------------------------- ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8) 101.......1056................2013............... 102.......2148................2594............... 103... ---------------------------
Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest: ------------------------------------------- ........A...............B..................C.................D............ 1 393E 2 stroop.xpt 3 Wed Jan 1, 12:33:00 2008 4 5 6 Trial Name.....Trial No..... Error Code.....ReactionTime 7 introduction..... 1................C............561243 8 practice.......... 2................C............... 2062 9 red-worried........14.............C................1001 (*the first practice trial) 10 (9 more practice trial rows, deleted here) 19 instructions........3..............C................5000 20 red-chicken......19..............E............... 1205 21 blue-tense.......32...............C.................782 * (46 more actual trial rows, deleted here) --------------------------------------------------
To clarify: -The variable 'Trial Name' indicates nominally what stimulus was presented for each trial. -The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.) -A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.
I have always saved each macro on a single workbook, saved the workbook using the macro name, so I can go use that spreadsheet for a specific issue. Now, however, my job role is changing to where I have to do a lot of things like get a file from Bob . . . slice and dice and then forward it on to Carol, etc. Then I have to save spreadsheet A to this directory, spreadsheet B to that directory . . . .
I am sure there is, I just can't get my head around it to have one spreadsheet that has ALL my macros that would be available to me on other spreadsheets? I tried using "show macros in all open workbooks"
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
I have a macro that formats an imported report and I have inserted it's code into several other routines I have written.
Since there is always the possiblity of changes occurring, is there a way to refer to this macro in a second (or third or fourth) routine so that if a change has to be made to the original macro, I won't have to change it everywhere?
I have been asked to create a spreadsheet that has a front sheet where you can free type search criteria and then it will populate rows below with all search results from the remaining sheets within the workbook, for example;
In cell N15 I type the search criteria and hit a button
In cells F31-O31 it returns the following information;
CategorySub-CategoryCourseDrop Down Option 3SolutionFurther information or support
I am using the code below to open multiple workbooks in their own instance of Excel.
On my system, as well as most of the ones I've tested it on, enabling macros in the "parent" workbook, i.e. the one that contains the code, leaves them automatically enabled in the new session of Excel, as though the settings are carried over.
However, recently I've found a few systems where this is not the case. Enabling macros in the parent workbook opens the others, and then I have to enable macros again in each of the new sessions. Is there a way to fix this? All of the systems mentioned above have macro settings set to "disable with notification" and this cannot be changed.
I'm having difficulty assigning multiple macros to an active x combo box. I want a different macro to run every time the user selects a different location from the combo box. I can assign one macro to the button no problem, but don't know how to incorporate more. The two separate macros I want to run are: