Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2011, 08:17 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 08-08-2011, 03:42 AM
OTPM OTPM is offline Code to add dates Windows 7 32bit Code to add dates Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Terri
Can you post a copy of your workbook containing the code and we can have a look for you.
Tony(OTPM)
Reply With Quote
  #3  
Old 08-08-2011, 04:07 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default

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
Attached Files
File Type: xlsm CC SOW.xlsm (89.1 KB, 11 views)
Reply With Quote
  #4  
Old 08-08-2011, 08:46 AM
OTPM OTPM is offline Code to add dates Windows 7 32bit Code to add dates Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #5  
Old 08-11-2011, 03:54 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default

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
Attached Files
File Type: xlsm CC SOW.xlsm (151.3 KB, 13 views)
Reply With Quote
  #6  
Old 08-12-2011, 06:23 AM
OTPM OTPM is offline Code to add dates Windows 7 32bit Code to add dates Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #7  
Old 08-17-2011, 07:31 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 08-18-2011, 10:55 PM
Catalin.B Catalin.B is offline Code to add dates Windows Vista Code to add dates Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 08-23-2011, 01:09 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 08-23-2011, 03:38 AM
Catalin.B Catalin.B is offline Code to add dates Windows Vista Code to add dates Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 11-08-2011, 09:51 AM
terricritch terricritch is offline Code to add dates Windows 2K Code to add dates Office 2007
Novice
Code to add dates
 
Join Date: Aug 2011
Posts: 6
terricritch is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to add dates Calendar without actual dates parnol01 Project 1 12-16-2015 09:30 AM
Code to add dates Calculating dates Daria11 Word VBA 1 06-08-2011 06:54 PM
Code to add dates Sequential Dates in Word Ultrap3 Word 2 02-04-2011 02:53 PM
Code to add dates Calculated dates. Ziggy-R Word 10 09-28-2010 01:42 AM
Adding dates mkkram Word 0 11-27-2006 09:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft