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

[Guest]

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 Staff]

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

 

[Bob Phillips]

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.

 

[Guest]

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

 

[Harald Staff]

Hi Pank

Everything inside quotes mean "literally". So end them before a variable:

"Y:\Skip Register\Quarterly Charging Period" & MyQ

HTH. Best wishes Harald

 

[Guest]

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

 

[Bob Phillips]

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.

 

[Ron Rosenfeld]

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

 

[Harald Staff]

'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

 

[Dana DeLouis]

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

 

[Guest]

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

 

[Guest]

Thank you to Harald, Bob, Ron and Dana for the solutions you supplied.

Works a treat.

Regards

Pank