Date Activated Macro
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Date Activated Macro

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    On the 1st day or each month, it will take the last entry in column H, add a fixed amount of currency to it and insert the sum into the 1st empty cell in column H. Within the same row, it would insert the current date into column B, and insert a zero into column C.

    Any assistance will be greatly appreciated.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Woodier

    How about

    Code:
    Private Sub Workbook_Open()
    
    If Month(DateAdd("d", -1, Date)) <> Month(Date) Then
    
        With Sheet1.Range("H65536").End(xlUp)
           .Value = Sheet1.Range("H65536").End(xlUp) + 25.3
           .Offset(0, 6) = Date
           .Offset(0, 5) = 0
        End With
     
    End If
     
    End Sub


    Placed in the Private Module of ThisWorkbook.



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training





    [ This Message was edited by: Dave Hawley on 2002-04-01 00:31 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Dave,but your Macro did not produce any results.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works fine when I run it! Don't forget it is set to only run on the first day of each month. You may also need to change the CodeName ("Sheet1") to suit.

    The Offsets should also be negatives. IE
    .Offset(0, -6) = Date
    .Offset(0, -5) = 0


    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-04-01 21:14 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    I attempted to run your Macro on April 1. I'll try it again, with your suggested name change, on May 1.

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    woodier, no need to wait that long! Just change the:

    Month(DateAdd("d", -1, Date)) <> Month(Date)

    To

    Month(DateAdd("d", -2, Date)) <> Month(Date)


    This will make the Month of today's date <> to the date of Today -2, If the date there is the second of the Month.



  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Dave,

    The Macro works, after I changed the names of the Sheets.

    My sincere thanks.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com