I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
View 3 RepliesI require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
I want to create a long list of 10,000 random numbers between -100 and +150, but do it so that the average of all the random numbers equals 20.
View 9 Replies View RelatedI sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies View RelatedI have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
I have a workbook which has a variety of command buttons with assigned macros, one of which is a product detail button which redirects to a new worksheet.
Is it possible to be able to click on the button, display a new window (the new worksheet), but only display a section of it?
For example, I have 5 products. Each of the five products are detailed on the same worksheet and I have a button for each. I would like Button 1 to open up the details for product 1 only and button 2 to open up product 2 details etc.
I could go to individual sheets, but if I had 2000 products, the book would be a little large.
I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.
Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.
The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.
A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name
[Code] .......
A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter
The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column:
000122042009ABCDEFG00567
Required result:
0001|22042009|ABCDEFG|00567
when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)
Applied Macro:
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets Id like to query data for each day and find an average value based on the time and then copy that result into another sheet. Im not sure if Ill need 2 macros for this or if one can be used.
Heres a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input 1:45 or 0:45 into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. Im flexible
1:45:
If I input 1:45, then Id like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I have a list of 200,000 e-mail addresses in a excel document in column A. What i am trying todo is find all the duplicate e-mail addresses within the list that appears more than four times.
I have tried this forumla in colunm B so it lists how many times the record appears: =countif($A$2:$A$244270,A2) which works fine and then a number appears next to each record. Then i pressed the sort filter to arrange all the records in number order so all the 5's are at the top of the worksheet but that does'nt always list them in the correct number order.
Basically all i want todo is find all the records that appear in my list more than five times then make a list of just one of those 5 records so i can remove them from my e-mail system.
I have a list of variables created by a user like this:
BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59
Seperately I have data from a third party like this:
DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.
I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.
I'd like to be able to repeat a list of names X number of times.
Example: Repeat count = 3
Name1
Name2
Name3
Name4
becomes
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
=COUNT(
1/FREQUENCY(
MATCH($A$1:$A$50,$A$1:$A$50,0)
),
ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))
+1))
I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.
So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.
Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.
I'm looking for the formula that returns 5.
And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.
I have two coworkers who input a list of times spent throughout the week into a basic 5x5 grid (daysof the week x 5 employees) into their own seperate workbooks. I then take those workbooks and copy and paste them on to my own master work book. Is there any way to get their data to auto populate on to my spreadsheet?
View 3 Replies View RelatedI used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.
View 2 Replies View RelatedColumn A: multiple names of people, almost all names occur multiple times Columns B: counts how many times the name occurs in total
What I want in column C: if there are e.g. 18 times the name 'Peter'. I'd like to see in columns C that the first 'Peter' found has 1, the second Peter found has 2, etc; this offcourse for all names...
I have a drop down list full of options for my user to choose. 3 of those options I have message box codes built so that if they are selected, the message box will pop up. However, the first one in the code makes the user click the "ok" box 3 times before it goes away, the second one makes them click 2 times, and the third they just have to click once. to make it where they only need to click "OK" once on each of those?
Here is the
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngIntersect As Range
Dim rngCell As Range
Dim sMsg As String
Set rngIntersect = Intersect(Target, Range("j24:j31"))
If Not rngIntersect Is Nothing Then
For Each rngCell In rngIntersect
If CStr(rngCell.Value) = "E Checking - 105" Then
sMsg = "Please have customer complete E-Statement Application for E Checking - 105 accounts"
Exit For
End If
Next rngCell
End If
I have a list of names for my wedding and am making place cards out of scrabble pieces and need to know how many times each individual letter appears in the 150names to know how many of each letter to buy
View 5 Replies View Relatedthe number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies View RelatedI'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.
View 9 Replies View RelatedI have a filtered list and want to count the number of times each value appears in the filtered list. I also have other hidden rows that I do not want to include in the count and the filtered rows should not be included in the count either. I have tried countif, pivot tables and subtotal .
View 9 Replies View RelatedI have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.
I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.
Here is my problem:
I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)
Example of data (Where "/" delienates column seperation):
Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain
6-1-06 12:15 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:30 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:45 am / 45 / 35 / .0001/ 95 / 5 / 7 / 3 / 0
6-2-06 6:00 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-2-06 6:15 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
So what I need to do is this:
1) Find which data times are missing
2) Add the appropriate amount of rows in between where the missing data would be
3) Add the correct dates to the new rows
4) Add "N/A" to the columns that have no data
You can see that my date and time are formatted in mm/dd/yy hh:mm
I found this on the website and was trying to modify it to my needs:
Sub InsRow()
Dim c
but, after subtracting those days when they fell between Holidays / vacation periods.
See question, in details, inside the attached workbook.
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
View 4 Replies View RelatedStruggling a bit on this one! I have tried the index function, but doesnt quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is Index is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
I am having a set of names in one column and would like to print various combination of names in to another column. In sample data set, there are 30 names of entries in column B and like to print 4 data set i.e. 1st three set of combination of column B print into the column C similarly for other set of six, twelve and all kind of combination. Please find the detail in attached excel sheet.
View 3 Replies View Related