I have a table like the one below:
Day Night
2/26/2007DC
2/27/2007AB
2/28/2007AB
3/1/2007 CD
3/2/2007 CD
3/3/2007 BA
3/4/2007 BA
Each letter represents a 12 hour shift (ex. D team has 36 hours for the week). If I need coverage on 2/27/07 on the day shift, I need to find a team that is working 36 hours for that week and was not scheduled to work the shift before or the shift after. (Example, on 2/27/07 A team needs coverage. The only teams working 36 hours are D and C. C team work the previous shift. So the code would input D in a cell).
Attached is the file wherein I am trying to make the scheduling gantt for UAE calendar where the weekends are Friday and Saturday. I am not able to format sundays as workdays some how through my conditions.
I have columns with the dates on the top, and staff listed on the rows to the left of the speadsheet. I must have 4 H's (indicating a base location), and 4 F's (indicating another base) in the column below the date. I want to have a cell, just above the date which will highlight red if there are any less than 4 H's and 4 F's. If there are 4 H's and 4 F's in the column (indicating full staffing levels), then I want the same cell to turn green, indicating that the day is OK. The red highlight would be a quick scan for supervisors to look at and see if we are deficient on staffing, and will prompt us to look for people who can be the missing H or F.
Is there a way to set the ontime application to run even when excel is turned off? I'd like to be able to refresh a data table every morning at 6:30, even if excel is not on. Ideally, if even my computer isn't on. The code below works, but only if the sheet is open to the exact worksheet.
Option Explicit Public RunWhen As Double Public Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = TimeValue("09:54") Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _ schedule:=True End Sub
I have a simple macro that I run when I hit "Ctr Q".
How do I ensure that this macro runs every 5 minutes automatically from the first time I hit "Ctr-Q". Currently, it only runs once and then stops.
Here's my macro for your view:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 08-09-2006 by Scott ' ' Keyboard Shortcut: Ctrl+q ' Range("B6").Select ActiveCell.FormulaR1C1 = "NOW()" Range("B6").Select ActiveCell.FormulaR1C1 = "=NOW()" Range("A4").Select End Sub
I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week
Open Close Monday8:3019:00 Tuesday8:3019:00 Wed8:3019:00 Thur8:3021:00 Fri8:3019:00 Sat9:0018:00 Sun9:0018:00
Doctors have 5 days on and then 2 days off.
The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times. Booked Random Waiting Doctors 8:30 - 10:00Morning Hours55%45%30min5-7 10:00 - 13:00Increasing80%20%45min7-8 13:00 - 18:00Busy 100%01.5hr8+ 18:00 - 19:00Decreasing70%30%1hr6-7 19:00 - 21:00Night 40%60%30min5+
There are 3 types of patient visits ShortDoubleTriple %0.810.140.05 Minutes153045
58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.
and finally - doctors cant work for more than 4 hours without a break. brek details are below HoursBreak <4hrs0 4 - 5hrs10min 5 - 8hrs2*10 mins - 45min lunch 8+2 * 10min 2*45min lunch
I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.
I'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
I have an excel spreadsheet that contains customer data exported from Quickbooks. In that file there is a column called CType (Column G) which contains the scheduling cycle for that customer. There are eleven different possible schedules, including 15xYr, 1xMo, 2xMo, EOM (every other month), EOW (every other week), EOW-S / 1xMo-W (every other week between 5/1 and 10/31 and once a month during the rest of the year), EOW-Th (every other week but must be on a Thursday), ETW (every third week), On Call, W, Q.
Although the number of records changes daily, on average there are about 950 rows of data in the spreadsheet.
I need to set up an array (?) for the values in column G starting with G2 through the last non-blank row (all records will have data in column G), and calculate the next scheduled service date based on the schedule type shown in column G and the last service date which will appear in column M of that row. The last thing I need to consider is whether there is a value in Column N, which would represent a hard-entered ‘Next Scheduled Service Date’. If I find a date in that column it needs to over-ride the calculated value. My assumption to this point is that I should use a new column T to hold the value of the calculated schedule date. There are a few other details, but rather than confuse this whole thing further I’ll stop there.
What I’m struggling with is the most efficient means of running this macro. I’ve been looking at many, many threads over the past few weeks, reading VBA Programming for Dummies (which I apparently am), and reviewing other resources.
I am wanting to have a schedule posted as 8:00am to 5:00pm to show up in a format that shows the interval every thirty minutes with either a 1 or a zero so that I can calculate each intervals total staffing. Also if a break or lunch is entered I would like it to show a zero or .5 in the field (.5 being for 15 mins and 0 for anything taking the full 30 mins).
So I'm having a problem trying to make this scheduling sheet.. What I did was row a would be the employee list then b,c,d and so on are mon-sun Originally in b1 I would put like 9am-5pm and c1 8-4 and so on and in row i I put the total and added a sum function but somehow it wasn't able to calculate. So then I redid the whole thing this time in row b1 i put 9am and c1 5pm and so on and again I put the total and added the function and it still came out wrong..
I need to dispatch 10 technicians across the US starting each Monday. They go to 10 different customers for the whole week. I know the distance between each technician and each customer. What I would like to do is assign each technician to a customer so that the total distance traveled for all the technicians is minimized. I know that if it was only 3 techs and 3 customers (as an example), I could build a table of all the possible combinations (3 factorial or 6 permutations), determine the distance for each permutation and select the minimum. I'm not sure that doing this method with 10 techs is feasible as the number of permutations is 10 factorial or over 3 million possibilities. Anyone see a workable solution?
I've include a spreadsheet with customers, technicians and the distances between them!
I'm trying to create an Employee Scheduler. I want my employees (1 technician per row for a total of 10 rows) in the 1st column then the monthly calendar to the right (1 cell/day). That is what you'll see when you open the worksheet. Then, down below I set up my Data Area where I input my project, start date, end date, technician (where each tech is assigned a unique number), and other data across a row of cells. This could be infinite (or at least up to the very last row).
I modified a free Project Scheduler I got from XL-EasyGantt to become my Employee Scheduler but am having some difficulties getting it to do what I want. The project Scheduler basically works by entering (on the same row) the project (or task), the start date and finish date and then the cells get automatically filled in across those dates. This allows for overlapping of tasks but I want to create an Employee Scheduler so it doesn't allow overlapping or schedule a technician to start a new project before he/she has completed the one their on.
The main function which is in each cell of the monthly calendar is as follows: =IF(OR(AND($G28>=K$4,$G28<L$4),AND($M28>=K$4,K$4>=$G28)),IF(AND($E11="x",$M28>=K$4,$M28<L$4),"x",IF($E11=".",".","..")),"")
My problem: I want to be able to look at the Main Schedule and see when each Technician is scheduled for a job (or multiple jobs) for any given month. I want the function to look in my Data Area and assign the start date and end date of a project and the corresponding technician number and fill in the date cells across from their name in the Schedule above.
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
I have an excel file having part code,name,vendor and Qty ( Quantity ).
My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Public IE As Object Sub Sample() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.
1. Dimensions and Variables
2. Data
3. Processing the Data
4. Rules of analysis
5. Analysis
6. Formatting
I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?
I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water SW = Saline eater CW = connate water MW = Meteoric water
numeric code as follow
FW=1 SW=2 CW=3 MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
