Formula for current month minus one = Quarter number in a macro.

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?

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" <[email protected]> skrev i melding
news:[email protected]...
> 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?
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
>
>
>

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" <[email protected]> skrev i melding
news:[email protected]...
> 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"

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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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"
>
>
>

"Pank" <[email protected]> wrote in message
news:[email protected]...
> 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.

On Tue, 21 Jun 2005 01:53:34 -0700, "Pank" <[email protected]>
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

"Pank" <[email protected]> skrev i melding
news:[email protected]...
> '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

> 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" <[email protected]> wrote in message
news:[email protected]...
>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?
>

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" <[email protected]> skrev i melding
> news:[email protected]...
> > '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
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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?
> >
>
>
>