+ Reply to Thread
Results 1 to 16 of 16

Getting a mismatch when trying to evaluate dates

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Getting a mismatch when trying to evaluate dates

    So I have a short VBA script that runs through all of my data in one sheet and determines if the first column (last name) matches a user specified (via form text box) last name, then the second column (first name) matches a user specified first name, and takes all rows which match and pastes them into a second sheet. This works great. I also have a form text box that allows the user to specify how far back they want data in years. The 5th column (E) has dates in it. I have tried several things and they always throw errors at me. Most commonly I get a mismatch error when trying to initialize a date from the cell as a date, which seems like it should just already be.

    Here is all of the code
    Please Login or Register  to view this content.
    The parts I have commented out are the parts that deal with the dates. I get errors when they aren't commented. As it is now, the line
    Please Login or Register  to view this content.
    gives me a mismatch error. Currently the entire column E is stored as a date. I even went through each row and checked for poor data entry. I found some blank cells and thought that might be it but no luck still. I have also tried evaluating the dates as integers to see if that would work but I got some weird errors that highly discouraged that path.

    Any suggests on how best to evaluate if the cells date is older than the specified years back.

    For clarification, if the user inputs 3 into the YearsBack textbox, then I want to see if a cell in the sheet is greater than today's date - 3 years.

    Thanks,
    Troll

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

    Re: Getting a mismatch when trying to evaluate dates

    Troll

    Are there any errors or unusual characters, eg Chr(160), in the date column?

    Which row do the dates start in?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    The actual data starts on row 4, where I started my loop. Before that there is a merged row 1&2 sheet name and row 3 is headers. There are no unusual characters, I have triple checked it now after finding some odd balls the first time through. A couple were formatted as text (left justified and had a little green triangle), I fixed them. Every single cell in that column is a date mm/dd/yyyy format. There are no more blank ones either.

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

    Re: Getting a mismatch when trying to evaluate dates

    Cells with a date formatted as text shouldn't be a problem, VBA will convert them.

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    Here, I did my best to duplicate the set up. There is a form in there too. It's not the exact same but there are 3 text boxes and a button just like that. Obviously I didn't go through naming and re-coding it, as that would take a lot of time. If you need it I can do my best. The excel sheet however is really close. Obviously just a lot smaller.

    Lets say I put in the form Marley, Bob, 5. I would want the line "Marley Bob 4/20/2014 Didn't show for work" to be copied to the other sheet. Right now I have it copying all of his lines over. I just don't see why evaluating a column of dates is giving me so much trouble, the rest of this took maybe 35min...
    Attached Files Attached Files

  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: Getting a mismatch when trying to evaluate dates

    There's no form, or code, in the attached workbook.

  7. #7
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    Ah, I thought maybe you just wanted to see the format of my workbook. Here is the macro enabled version with the form and the code. I realized I could just copypasta it for you.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    My bad, the form is called GetRecentInfrac on my other book so I got the obvious error that I need an object to hide. Sorry, I've gotten quite scatter brained trying to figure this out.

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

    Re: Getting a mismatch when trying to evaluate dates

    I can't replicate the error and the dates in column E look fine, mind you that's probably due to the dummy data.

    When you get the error in the real workbook with the actual data what is the value in Range("E" & i)?

    Also, is is possible the wrong sheet is active when you run the code?

  10. #10
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    Definitely on the right sheet when I run it. Did you remove the commented out parts? The code was commented so I could at least get some information out. There were 3 lines. How do I see what the value is in the range when I get the error?

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

    Re: Getting a mismatch when trying to evaluate dates

    Yes I commented out the line.

    You can see the value if you press Debug when you get the error, go to the sheet and look in row i (the loop variable's value) of column E.

    Or you could press Debug, open the Immediate Window (CTRL+G) and enter this.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    You were right, I found a date that was 13/5/2009. Obviously aren't 13 months. I changed it to 12 (hope that's what it was supposed to be XD) and it ran. But it grabbed dates that were more than 3 years old when I specified 3 years? Do you see any logical errors? I'm not sure how that "FindDate = DateAdd("yyyy", YearsBack, dtCurrent)" line actually works. I set YearsBack = 0 - YearsBack and tried it again with no luck.

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

    Re: Getting a mismatch when trying to evaluate dates

    This will add YearsBack years to dtCurrent.
    Please Login or Register  to view this content.
    For example.
    Please Login or Register  to view this content.
    If you want to subtract years use -YearsBack.
    Please Login or Register  to view this content.
    0

    PS I'm using the long date format avoid confusion, my local date format is dd/mm/yyyy.

  14. #14
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    Awesome, you were able to answer the question by giving that example. Where I found the code for that DateAdd function they never specified that I needed to set dtCurrent = Date as you had done there. It's now working properly. I guess I was taking 3 years from some random value that wasn't there and then excel was just assuming I wanted everything. Thanks so much Norie. I guess you were the one who helped me out last time because I had to find someone else to +rep to be able to give some more to you =]

    PS, so you mean you are using the logical date format. haha. I hate mm/dd, simply doesn't make sense.

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

    Re: Getting a mismatch when trying to evaluate dates

    Never heard it called the 'logical' date format but that's what I've always used.

  16. #16
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting a mismatch when trying to evaluate dates

    I think pretty much everywhere uses that format except in the US. We put the month first, which just doesn't make sense. That would be like writing numbers with the tens column first then the ones column then the hundreds column. I think it's made to model when we write January 18th, 2011 or something. I just think dd/mm/yyyy is the most logical way to write it so that when the days are incremented above 30/31/(28/29) you move over and increment the months, until they hit 12 then you move over again and increment years.

    Anyways, enough of my ranting, thanks again for being so helpful!

+ 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. Conditional formatting with IF, AND statements to evaluate due dates
    By Ragnazidnar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2013, 11:16 AM
  2. VBA: Using Evaluate / Countifs Combination to Compare Dates
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 02:02 PM
  3. [SOLVED] MACRO - IF Statement to evaluate if Date falls BETWEEN to Dates - Please help.
    By tdguy09 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-05-2012, 07:17 PM
  4. Code to evaluate FIFO for multiple items and dates.
    By BillRush in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2011, 11:09 PM
  5. Replies: 3
    Last Post: 12-14-2007, 11:51 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