Re: Excel change by 1 month
Re: Excel change by 1 month
- Subject: Re: Excel change by 1 month
- From: Paul Berkowitz <email@hidden>
- Date: Mon, 15 Dec 2008 00:04:32 -0800
- Thread-topic: Excel change by 1 month
Title: Re: Excel change by 1 month
tell application "Microsoft Excel"
repeat with i from 3 to 21
set d to value of range ("A" & i)
set d to my MonthAdd(d)
set value of range ("A" & i) to d
end repeat
end tell
on MonthAdd(d)
set m to ((month of d as integer) + 1)
set ddd to day of d
if m > 12 then
set m to m - 12
set year of d to (year of d) + 1
end if
if {m} is in {4, 6, 9, 11} and ddd = 31 then--AppleScript treats "Apr 31" as May 1, VBA as Apr 30
set day of d to 30
end if
set month of d to m
if m = 2 and month of d as integer = 3 then --AppleScript treats "Feb 31" as Mar 3, VBA as Feb 28 or 29
set day of d to 1 -- Mar 1
set d to d - (1 * days) -- last day of Feb
end if
return d
end MonthAdd
--
Paul Berkowitz
> From: Mail <email@hidden>
> Date: Sun, 14 Dec 2008 22:48:13 +0000
> To: Paul Berkowitz <email@hidden>
> Subject: Re: Excel change by 1 month
>
> Paul
>
> I have copy the whole macro out except the "next" command and
> the lines which repeats the lines already sent but for a different
> range.
>
> On 14 Dec 2008, at 21:31, Paul Berkowitz wrote:
>
>> On 12/14/08 11:10 AM, "Mail" <email@hidden> wrote:
>>
>>> I have just upgraded to Office 2008 and rewriting my marco's in
>>> Applescript because Office now doesn't support VBA anymore. I want to
>>> change dates in my expenses to the next month. I was using the below
>>> macro.
>>>
>>> Sub NextMonth()
>>> Dim i As Integer
>>>
>>> For i = 3 to 21
>>> Sheets("Expenses").Range("A" & i) = dateadd("m", 1,
>>> sheets("Expenses").Range("A" & i))
>>>
>>> End Sub
>>>
>>> Can anyone help with this.
>>>
>>> Also if I want to put a line in to describe what the Script is doing
>>> at at any point, how do I have to start it.
>>
>> As I no longer have access to Excel VBA in 2004 or earlier, I'm
>> wondering if
>> that 'dateadd' function was a built-in VBA Method (I really don't
>> think so)
>> or was calling another function in your macro module named
>> "DateAdd()".
>> Check your full macro. If you see a DateAdd() tell us what the code
>> for it
>> is. In fact, best just transcribe the code for the entire module, as
>> there
>> may be more dependencies (other subsidiary subroutines).
>>
>>
>> --
>> Paul Berkowitz
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> AppleScript-Users mailing list (applescript-
>> email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>> uk
>> Archives: http://lists.apple.com/archives/applescript-users
>>
>> This email sent to email@hidden
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden