Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have a macro that does several things. The last step should be to save the
file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month €“ 1)/3)). If current month €“ 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
#2
![]() |
|||
|
|||
![]()
Hi Pank
Private Function MyQ() As Long MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4) End Function Sub test() MsgBox MyQ End Sub HTH. Best wishes Harald "Pank" skrev i melding ... I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month - 1)/3)). If current month - 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
#3
![]() |
|||
|
|||
![]()
Private Function QuarterNum(Optional myDate) As Long
If IsMissing(myDate) Then myDate = Date QuarterNum = Int((Month(DateSerial(Year(myDate), Month(myDate) - 3, 1)) + 2) / 3) Debug.Print QuarterNum, Format(myDate, "dd mmm yyyy") End Function Sub test() QuarterNum DateValue("12/01/2005") QuarterNum DateValue("12/02/2005") QuarterNum DateValue("12/03/2005") QuarterNum DateValue("12/04/2005") QuarterNum DateValue("12/05/2005") QuarterNum DateValue("12/06/2005") QuarterNum DateValue("12/07/2005") QuarterNum DateValue("12/10/2005") End Sub For the fileformat, omit that property, it will default to the format of the version of Excel being used. -- HTH Bob Phillips "Pank" wrote in message ... I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month - 1)/3)). If current month - 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
#4
![]() |
|||
|
|||
![]()
Harald, Bob,
Firstly many thanks for your prompt responses. I have used Harald's solution (nothing personal Bob) and I need to know how to get the symbolic MyQ into the file save. I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced it as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as "Y:\Skip Register\Quarterly Charging Period MyQ" Thanks Pank "Bob Phillips" wrote: Private Function QuarterNum(Optional myDate) As Long If IsMissing(myDate) Then myDate = Date QuarterNum = Int((Month(DateSerial(Year(myDate), Month(myDate) - 3, 1)) + 2) / 3) Debug.Print QuarterNum, Format(myDate, "dd mmm yyyy") End Function Sub test() QuarterNum DateValue("12/01/2005") QuarterNum DateValue("12/02/2005") QuarterNum DateValue("12/03/2005") QuarterNum DateValue("12/04/2005") QuarterNum DateValue("12/05/2005") QuarterNum DateValue("12/06/2005") QuarterNum DateValue("12/07/2005") QuarterNum DateValue("12/10/2005") End Sub For the fileformat, omit that property, it will default to the format of the version of Excel being used. -- HTH Bob Phillips "Pank" wrote in message ... I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month - 1)/3)). If current month - 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
#5
![]() |
|||
|
|||
![]()
Hi Pank
Everything inside quotes mean "literally". So end them before a variable: "Y:\Skip Register\Quarterly Charging Period" & MyQ HTH. Best wishes Harald "Pank" skrev i melding ... Harald, Bob, Firstly many thanks for your prompt responses. I have used Harald's solution (nothing personal Bob) and I need to know how to get the symbolic MyQ into the file save. I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced it as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as "Y:\Skip Register\Quarterly Charging Period MyQ" |
#6
![]() |
|||
|
|||
![]()
Harald,
Sorry to be a pain in the bo**om. The end part of the macro is as follows:- test ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Urban Vision Period" & MyQ, Password:="", _ WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False Unfortunately, it does not insert the numeric represented by MyQ into the file name. Additionally, you supplied:- 'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4) Can you please clarify whether the digit 4 is correct or should it be 3 to represent quarters (i.e. every 3 months)? And lastly, if you would be good enough to explain the above it would aid me greatly. Any ideas? Many thanks Pank "Harald Staff" wrote: Hi Pank Everything inside quotes mean "literally". So end them before a variable: "Y:\Skip Register\Quarterly Charging Period" & MyQ HTH. Best wishes Harald "Pank" skrev i melding ... Harald, Bob, Firstly many thanks for your prompt responses. I have used Harald's solution (nothing personal Bob) and I need to know how to get the symbolic MyQ into the file save. I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced it as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as "Y:\Skip Register\Quarterly Charging Period MyQ" |
#7
![]() |
|||
|
|||
![]() "Pank" wrote in message ... Harald, Bob, I have used Harald's solution (nothing personal Bob) and I need to know how to get the symbolic MyQ into the file save. I am offended <vbg They actually give different answers depending upon the interpretation, so you need to use the one that conforms to your requirement Date Harald Bob 12 Jan 2005 4 4 12 Feb 2005 1 4 12 Mar 2005 1 4 12 Apr 2005 1 1 12 May 2005 2 1 12 Jun 2005 2 1 12 Jul 2005 2 2 12 Aug 2005 2 2 12 Sep 2005 3 2 12 Oct 2005 3 3 12 Nov 2005 3 3 12 Dec 2005 3 3 I also answered the fileformat bit. |
#8
![]() |
|||
|
|||
![]()
On Tue, 21 Jun 2005 01:53:34 -0700, "Pank"
wrote: How can enter effectively enter a formula in a macro which is along the lines of ((current month – 1)/3)). If current month – 1 = 0 then set current month to 12 and undertake the calculation again. Qrtr = DatePart("q", DateAdd("m", -1, Date)) Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? I do not see that as an available choice in XL2002, so I would guess not. --ron |
#9
![]() |
|||
|
|||
![]()
"Pank" skrev i melding
... 'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4) Can you please clarify whether the digit 4 is correct or should it be 3 to represent quarters (i.e. every 3 months)? Doh ! Really sorry about that. This is more like what I intended: MyQ = 1 + Int((Month(DateSerial(Year(Date), Month(Date) - 1, 1)) - 1) / 3) it will return for each month jan 4 feb 1 mar 1 apr 1 may 2 jun 2 jul 2 aug 3 sep 3 oct 3 nov 4 dec 4 but it may not be what you wanted. Best wishes Harald |
#10
![]() |
|||
|
|||
![]()
April to get Quarter number to 1, run macro in July to get Quarter number
to 2, ... Just another idea. If you run your macro anytime in July, Aug, or Sep, and still want the previous quarter, perhaps another option... Qrtr = 499 Mod (Format(Date, "q") + 4) HTH -- Dana DeLouis Win XP & Office 2003 "Pank" wrote in message ... I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month - 1)/3)). If current month - 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
#11
![]() |
|||
|
|||
![]()
Harald,
You must have missed the first part of the post which was:- The end part of the macro is as follows:- test ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Urban Vision Period" & MyQ, Password:="", _ WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False Unfortunately, it does not insert the numeric represented by MyQ into the file name. ANy Ideas? Thanks Pank "Harald Staff" wrote: "Pank" skrev i melding ... 'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4) Can you please clarify whether the digit 4 is correct or should it be 3 to represent quarters (i.e. every 3 months)? Doh ! Really sorry about that. This is more like what I intended: MyQ = 1 + Int((Month(DateSerial(Year(Date), Month(Date) - 1, 1)) - 1) / 3) it will return for each month jan 4 feb 1 mar 1 apr 1 may 2 jun 2 jul 2 aug 3 sep 3 oct 3 nov 4 dec 4 but it may not be what you wanted. Best wishes Harald |
#12
![]() |
|||
|
|||
![]()
Thank you to Harald, Bob, Ron and Dana for the solutions you supplied.
Works a treat. Regards Pank "Dana DeLouis" wrote: April to get Quarter number to 1, run macro in July to get Quarter number to 2, ... Just another idea. If you run your macro anytime in July, Aug, or Sep, and still want the previous quarter, perhaps another option... Qrtr = 499 Mod (Format(Date, "q") + 4) HTH -- Dana DeLouis Win XP & Office 2003 "Pank" wrote in message ... I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quarter number to 1, run macro in July to get Quarter number to 2, run macro in October to get Quarter number to 3, run macro in January to get Quarter number to 4). How can enter effectively enter a formula in a macro which is along the lines of ((current month - 1)/3)). If current month - 1 = 0 then set current month to 12 and undertake the calculation again. Any help offer is most appreciated. Thank You Pank Lastly, if my version of Excel is 2000, should I substitute 2000 in place of 9795 in the file format above? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Date formula | Excel Discussion (Misc queries) | |||
formula to have each new invoice automatically have the next numb | Excel Discussion (Misc queries) | |||
what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Formula to convert to month | Excel Worksheet Functions |