+ Reply to Thread
Results 1 to 8 of 8

VBA Excel formula: Month(DateValue(x & "1"))

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    VBA Excel formula: Month(DateValue(x & "1"))

    Hi,

    I'm sure I'm being a numpty and missing something here - but I can't get the following formula to calculate in VBA:

    =Month(DateValue(A1 & "1"))

    I've substituted the A1 reference for a string variable which contains the first three letters of the month, and tried application.worksheetfunction, application and evaluate, but keep geting a 'type mismatch' error which I guess is as a result of trying to convert a string value into a numeric one.


    Please Login or Register  to view this content.
    Works fine as a fomula though so sure VBA can handle it, I'm just not speaking the same language it would appear!

    Any assistance appreciated.

    Thanks, TC

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    maybe something like this...

    Please Login or Register  to view this content.
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    VBA has it's own Month function

    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    I cannot explain why the VBA function DateValue does not behave exactly the same as the worksheet function DATEVALUE but it looks like you need a space between the month name and the day. Try this:

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    Hi All,

    Thanks for coming back on this. Jeff you are indeed right - I was missing the leading space and that was why it wouldn't work, how very strange.

    Norie, again thanks for the efficient response - I tried this but again I was missing the leading space as Jeff pointed out - hence the head scratching. I've gone with this code.

    Thanks again, much appreciated.

    TC

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    The code I posted had the space - take a look it's right in front of the 1.

    PS Month isn't available via Application.WorksheetFunction.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    Norie is, of course, correct. I tested the DateValue call but not Application.WorksheetFunction.Month, which I did not realize does not exist.

    TC1980, what is the line of code that is now working for you?

  8. #8
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: VBA Excel formula: Month(DateValue(x & "1"))

    Hi All,

    Sorry Norie - I wasn't very clear, that's what I meant - I tried the vba month function before I opened this thread, but as I was missing the leading space it didn't work. I would never have thought about a missing space, so presumed it was something else.

    Jeff, this is the code I'm using, as per Norie's post:

    Please Login or Register  to view this content.
    Thanks, TC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. excel formula for "next month same day"
    By Ubi bene .. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2014, 01:05 AM
  2. [SOLVED] Excel 2007: How to Convert "5/2/2013" to "May" then subtract a Month so it's "Apr"
    By Golom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2013, 02:00 AM
  3. Function or formula to convert "text" month to number of month?
    By Steve Vincent in forum Excel General
    Replies: 1
    Last Post: 02-06-2006, 05:35 PM
  4. Replies: 0
    Last Post: 02-04-2006, 12:50 PM
  5. Replies: 1
    Last Post: 02-04-2006, 12:19 PM

Tags for this Thread

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