+ Reply to Thread
Results 1 to 10 of 10

Using date variable in vba formula

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Using date variable in vba formula

    Hi,
    I've been unsuccessful in finding a solution via the internet. Unfortunately, I've spent hours trying and I'm really stuck. This seems uncomplicated. I am trying to run the code below.

    Sub testDate()

    Dim LYear As Date
    Dim testDate As Date

    LYear = Year(R2)
    testDate = Date

    Sheets("List").Range("R2").Formula = "= " & testDate & ""

    Sheets("List").Range("S2").Formula = "=" & LYear & " "

    End Sub

    I get the following results.
    R2 - 1/0/1900
    S2 - 1/0/1900

    R2 should be today's date and S2 should be only the year (2021).

    R2 and S2 cells are formatted as Date (*3/14/2012).

    Greatly appreciate assistance with this.

    Karen

  2. #2
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,681

    Re: Using date variable in vba formula

    Dates are numbers that we see in date format thanks to cell formatting. For example, the date 6/28/2021 is the number 44375.
    Operate in code on numbers (in this case on integers).
    Please Login or Register  to view this content.
    Cell S2 should be formatted as General.

    Artik

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Using date variable in vba formula

    Hi Artik,

    Copied your code as written and changed S2 to General. Still not getting the result needed.
    R2 results to 1/0/1900 and S2 results to 1899

    Karen

  4. #4
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,681

    Re: Using date variable in vba formula

    Show in the attachment a sample workbook where it does not work.

    Artik

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Using date variable in vba formula

    Attached is the Excel sheet.

    Thanks for taking the time to review.
    Karen
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,681

    Re: Using date variable in vba formula

    It doesn't want to work because you didn't declare the variables, especially testDate must be a variable of the Long type. Without declaring, the variable takes the default Variant type. If a date is assigned to it (testDate = Date), the variable type automatically changes the subtype to Date. And that's where the problems begin.
    1. Delete the contents of cells R2: S2.
    2. Paste the code into the module in the same form as I gave it before.
    3. Run the testDateRange macro twice.
    After the second run, you should be fine.

    Artik

  7. #7
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Using date variable in vba formula

    Apologies. I should have caught that I hadn't declared the variables. I did as instructed and it works! THANKS

    A couple of questions.
    1. Why is it necessary to run the code twice before it works?
    2. I was stuck on how to use declared variables in a formula. In your solution, the declared variable is not in the "" marks for the formula. Totally get this when the formula is simple like in my example. However, what if you want to use a variable in a more complex formula?

    Example - The code below works great. But I want to replace Today() with the variable testDate. Tried various combinations based on what you provided with no success. I get the #NAME error in O2.

    Sheets("List").Range("O2").Formula = "=If(AND(Today() >= List!$L$2, Today() <= List!$M$2), N2, False)"

    L2 = 8/1/2020, M2 = 9/30/2020 (Both cells are formatted as Date - #3/14/2012)

    Karen

  8. #8
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,681

    Re: Using date variable in vba formula

    1. Why is it necessary to run the code twice before it works?
    This is due to the logic of the macro. Study this simple code by running it in Step Mode (F8 in the VBA Editor) and preview the contents of the variables after each line is executed. In this mode, run the procedure twice. Compare the differences between the first macro call and the second.


    Sheets("List").Range("O2").Formula = "=If(AND(Today() >= List!$L$2, Today() <= List!$M$2), N2, False)"
    Please Login or Register  to view this content.
    Today() is a worksheet function. In VBA, the equivalent is Date().

    Artik
    Last edited by Artik; 06-29-2021 at 06:17 AM.

  9. #9
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,681

    Re: Using date variable in vba formula

    Duplicate post. Please remove.

    Artik
    Last edited by Artik; 06-29-2021 at 06:17 AM.

  10. #10
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Thumbs up Re: Using date variable in vba formula

    Hi Artik,

    Thanks! Works great. Really appreciate the time you have taken. I learned a lot.

    I can see the difference when I ran the macro via Step Mode.

    Karen

+ 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. [SOLVED] Formula to sum variable number of values in a column variable by date lookup ...
    By SNP529 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-18-2020, 06:54 AM
  2. Excel table filter by date variable through data picker date variable
    By SamanH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2018, 01:09 PM
  3. Replies: 4
    Last Post: 07-29-2015, 02:43 AM
  4. [SOLVED] Formula needed for future date using variable text from 1 column and dates from another.
    By KennySJT in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-09-2014, 08:14 AM
  5. Replies: 3
    Last Post: 02-05-2014, 11:53 AM
  6. Make a Date variable equal a single-cell array formula
    By Shamala in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-07-2012, 06:29 PM
  7. sumifs formula with > operator on a date variable
    By Kaigi in forum Excel General
    Replies: 1
    Last Post: 02-01-2011, 06:46 AM

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