• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Excel change by 1 month
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

  • Follow-Ups:
    • Re: Excel change by 1 month
      • From: Doug McNutt <email@hidden>
  • Prev by Date: Re: What's so great about AppleScript, anyway? (was Re: Tell Blocks Considered Harmful…)
  • Next by Date: Re: Tell Blocks Considered Harmful (was Re: open for access)
  • Previous by thread: Re: Excel change by 1 month
  • Next by thread: Re: Excel change by 1 month
  • Index(es):
    • Date
    • Thread