Convert date string to serial number

I've been banging on this for a month. I had it working on a template using dates from December and then we updated the dates for January and it's not working. Here's where I'm having trouble:

Please Login or Register  to view this content.

Because I was having trouble finding the phrase 12/17/2013 (which was actually a formula and even specifying Lookin:=xlValues didn't work), I have hidden rows that display the dates as their date serials. This works great for other parts of the macro that need to find the dates. However, at those points the dates are being grabbed from a listbox which seems to be giving it to the variable holder as the serial number. This bit of code above is happening before the listbox is populated, so the dates are strings. How do I get the macro to convert those strings to the serials?

I found two different articles that said I should be able to use DATEVALUE but so far that doesn't seem to be working, either in the macro or in the worksheet as a formula. It seems to just return whatever it gets: 12/17/2013 in, "12/17/2013" out.
TechOnTheNet article
office.microsoft.com article

TIA,
ama

Try,

Please Login or Register  to view this content.

That gives me run-time error 13. A1 is the whole phrase "12/17/2013 to 12/24/2013" (or whatever date range was chosen when the file was pulled). So I moved it down and tried:

Please Login or Register  to view this content.

Still got type mismatch error.

Edit: Which just occured to me is probably because of the "/" making Excel see text rather than a date?

"12/17/2013 to 12/24/2013" can not be convert in to serial number.

Yep. Which is why my code, if you read it all, actually breaks out the dates.
This is the start date, 12/17/2013:

Please Login or Register  to view this content.

This is the end date, 12/24/2013:

Please Login or Register  to view this content.

BUT those aren't even used in my search string. What's used is the textbox entry, DtSt. The textbox value comes from a date picker userform. The date picker puts 12/17/2013 into the textbox, which is being passed to the variable as a text string, from what I can see.

Perhaps I should have dropped the xl.. variable stuff. I was in a hurry and just copied the whole section. My apologies.

Just for the record, I did go to the DtSt line and try:

Please Login or Register  to view this content.

and still got type mismatch.

I only two functions which can convert a string in to numbers.

Please Login or Register  to view this content.

I will try that in the morning. I'm lowly hourly and have to clock out . Thanks so much.

Good morning. I tried:

Please Login or Register  to view this content.

and changed the search line to:

Please Login or Register  to view this content.

and I'm getting error code 91. It's searching for 12/17/2013. When I hover over ControlPanel.tbxStartDte, I see the value as:

Please Login or Register  to view this content.

When I hover over DtSt, I see it's value as:

Please Login or Register  to view this content.

So CDate is working on the string and converting it.

But what I really need is how to get 12/17/2013 converted to its serial: 41625. All the dates displayed in the workbook are actually formulas and for whatever reason, the search always fails unless I'm searching for the serial.

Try to remove value and see what says

DtSt = CLng(ControlPanel.tbxStartDte)

I did. That's what's in my reply above. I even tried with value and still what it's giving me is 12/17/2013 without quotation marks.

is DtSt a variable? If so how is it defined?
Am I right that when you search, the code returns nothing as the data to be searched are formatted as dates while the Dtst is a string, so there is a mismatch?

It is and declared as date, and no the error that's coming back is 91 - the variable or with block is not set. I've stepped through hovering over every variable and they're all set.

Okay!

This line may be the culprit

Cells.Find(what:=DtSt).Activate

Dim rng as range
You need to set rng as an object. Like

Please Login or Register  to view this content.

Izandol - The worksheet displays a month of dates and all except the first date of the month are formulas: Date 1: 12/1/2013, Date 2: =previous cell + 1, Date 3: = previous cell + 1... and so on. Just FYI, if you're going to suggest changing LookIn to xlValues, I have and it still doesn't find the date.

AB33 - I'm not sure why setting the search to a range variable would matter. Could you explain please? I'm confused because, like I said before, I've used the same line in other parts of the macro successfully. In the meantime, I'll give it a shot.

It is the error message you are getting. I also wanted to test which line of the code errors.
Does the code errors on that line? The code should not error if the find function syntax is correct. Yes, it will not return a value if the find does not find match because there is a mismatch between dates and string.

So basically I've been using Find incorrectly *facepalm*. That was the line it was erroring on and with your changes it's working now :D. It finds the date and doesn't find it when it shouldn't. Thank you! Seriously :D.

amalfaro,
With the wonderful world of hindsight, it would have been easy to spot the error if we had looked at the entire code.
Yes, you should use set with find method.
You are welcome!