#1
|
|||
|
|||
Code to add dates
I have a spreadsheet that I inherited and am looking at updating info on it. A macro has been set to work out dates on a weekly basis based on number of sessions per week. What I am wanting to do now is change the code slightly so that the exact date fills in for each session rather than just the monday date. EG if a session runs on Monday and Thursday at present it only provides the date for the Monday but provides it twice whereas I now want it to provide the date for Thursday. I know excel works with adding day + 1 for each day but not sure how to convert to code to do that.
Hope someone can help. Thanks in advance Terri |
#2
|
|||
|
|||
Hi Terri
Can you post a copy of your workbook containing the code and we can have a look for you. Tony(OTPM) |
#3
|
|||
|
|||
Thanks, have attached a briefer version of the full file (only due to the size of the main file) but this does have the bits I am trying to sort.
Terri |
#4
|
|||
|
|||
Hi
In the Validate_Fields() Function change: If Not Weekday(Range("B7").Value) = vbMonday Then MsgBox "Start Date must be a Monday (week beginning)", vbInformation to If Not Weekday(Range("B7").Value) = vbMonday Or vbTuesday Or vbWednesday Or vbThursday Or vbFriday Then MsgBox "Start Date must be a Weekday", vbInformation I think this will give you what you want. I am unable to step through the code because there are missing sheets. Hope this helps. Good luck. Tony(OTPM) |
#5
|
|||
|
|||
Hi Tony
Thanks for this. I have copied your code over and it now just brings up the message 'start date must be a monday (week beginning)'. I have re-attached the file with other sheets included, I have managed to reduce the size by taking a lot of completed data out. On the Setup sheet and LP sheet I have added some notes in red that might explain a little more what is wanted. Thanks again Terri |
#6
|
|||
|
|||
Hi
Try commenting out the code that checks for a Monday and see if that gives you what you need. Hope that helps. Tony(OTPM) |
#7
|
|||
|
|||
Hi, sorry still not getting what I want out of this. Maybe I'm asking for it to do too much.
Thanks for all your help Terri |
#8
|
|||
|
|||
enter in cell B8 this formula =WEEKDAY(B7), and refer in your code to cell B8...
Now, this part of the code should work If Not Weekday(Worksheets("SETUP").Range("B8")) = 2 Then MsgBox "Start Date must be a Monday (week beginning)", vbInformation Range("B7").Select ValidateFields = True Exit Function End If |
#9
|
|||
|
|||
Hi
Thanks for this. It works by bringing through the date of week commencing, what I am wanting to do is on the SOW sheet where it brings in the date for each session, I want the appropriate date if that makes sense so that if a session is monday and thursday the second session date shows as thursday rather than the monday date. Also 05/09/11 is a monday but excel says it is a sunday. Terri |
#10
|
|||
|
|||
To remove the Monday errors, change this formula in B8 from: =WEEKDAY(B7) to =WEEKDAY(B7;1), and format cell B8 as a number, not as a date (be careful to the red number in formula: the results of the formula means: 1:sunday, 2 monday, etc; if instead of 1 you choose 2 in the formula, 1 is monday, 2 is for tuesday, etc; if choose 3, then 0 is for monday, 1-tuesday, etc)
An easy way to increase with 2 days the second lesson, is to add the red lines in your code: (which will overwrite the values in these cells) For intStudentSOWCounter = 5 To intTotalNumber Range("A" & intStudentSOWCounter).Value = Sheets("sow").Range("A" & intStudentSOWCounter).Value If IsEmpty(Range("A" & intStudentSOWCounter).Value) = True Then 'MsgBox "HOLIDAY" strRange = "A" & intStudentSOWCounter & ":" & "F" & intStudentSOWCounter Range(strRange).Select With Selection.Interior .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.249977111117893 End With End If Range("B" & intStudentSOWCounter).Value = Sheets("sow").Range("B" & intStudentSOWCounter).Value Range("C" & intStudentSOWCounter).Value = Sheets("sow").Range("C" & intStudentSOWCounter).Value Range("D" & intStudentSOWCounter).Value = Sheets("sow").Range("D" & intStudentSOWCounter).Value Range("E" & intStudentSOWCounter).Value = Sheets("sow").Range("F" & intStudentSOWCounter).Value Range("F" & intStudentSOWCounter).Value = Sheets("sow").Range("K" & intStudentSOWCounter).Value Next For intStudentSOWCounter = 5 To intTotalNumber Step 2 Range("C" & intStudentSOWCounter + 1).Value = Range("C" & intStudentSOWCounter).Value + 2 Next The macro will run until row 61 to 65, which has a different input dates in sow To overcome this, if you do not want to change format in data source, (sow), break the range: For intStudentSOWCounter = 5 To 60 Step 2 Range("C" & intStudentSOWCounter + 1).Value = Range("C" & intStudentSOWCounter).Value + 2 Next For intStudentSOWCounter = 66 To intTotalNumber Step 2 Range("C" & intStudentSOWCounter + 1).Value = Range("C" & intStudentSOWCounter).Value + 2 Next For rows 61 to 65, you need another approach. It's important to know if this type of data in sow sheet will stay on these lines, or can vary, in this case, this approach will not work for those lines |
#11
|
|||
|
|||
Thanks for all the help here, been playing a little more and still having a struggle but am now looking at going on further training specific to VBA so I can start to understand what some of the code means.
Terri |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calendar without actual dates | parnol01 | Project | 1 | 12-16-2015 09:30 AM |
Calculating dates | Daria11 | Word VBA | 1 | 06-08-2011 06:54 PM |
Sequential Dates in Word | Ultrap3 | Word | 2 | 02-04-2011 02:53 PM |
Calculated dates. | Ziggy-R | Word | 10 | 09-28-2010 01:42 AM |
Adding dates | mkkram | Word | 0 | 11-27-2006 09:18 AM |