+ Reply to Thread
Results 1 to 7 of 7

# of days in a month works in the sheet, but not in a macro

  1. #1
    Registered User
    Join Date
    01-29-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    15

    # of days in a month works in the sheet, but not in a macro

    Thank you for taking the time to read this and offer assistance.

    Through searching various forums, I've discovered the code to determine the number of days in a given month when the date is in cell A1:
    Please Login or Register  to view this content.
    This works great when I enter the formula into another cell on the worksheet.

    However, when I try to use it in a macro, I get a syntax error about a missing ")"...it wants it after the "Year" portion.

    I've tried 2 different ways (Dim: UseDate as Date, UseDays as Integer, and DateVal as Integer)
    1)
    Please Login or Register  to view this content.
    2)
    Please Login or Register  to view this content.
    Any solutions to be able to use this fine line of code in my macro?

    Thank you -
    Dan
    Last edited by dank_01; 07-09-2012 at 05:48 PM. Reason: Fixed typo

  2. #2
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: # of days in a month works in the sheet, but not in a macro

    Hi Dank,

    Your syntax was just slightly off - try;

    Please Login or Register  to view this content.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: # of days in a month works in the sheet, but not in a macro

    And day zero (0) is the last day of the previous month so you can just use:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: # of days in a month works in the sheet, but not in a macro

    Please Login or Register  to view this content.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: # of days in a month works in the sheet, but not in a macro

    Oh, and I forgot to say, Date in VBA returns the current date, hence the reason it didn't like your syntax.

    Regards, TMS

  6. #6
    Registered User
    Join Date
    01-29-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: # of days in a month works in the sheet, but not in a macro

    Thank you all for your assistance - got it working now (-:

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: # of days in a month works in the sheet, but not in a macro

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1