input box date type mismatch

[Guest]

Hi Guys - another newbie question. Seem to be a lot of similar ones around
this theme but can't see my problem excactly, so here goes:

User input date is used to find a match in a range of dates in the
following code:

res = InputBox("Please enter Start Date")
If IsDate(res) Then
dt = CDate(res) 'convert response to a date type
res1 = Application.Match(CLng(dt), Rows(6), 0)
rest of code

This works a treat (Thanks Tom Oliver!) in one application,
but have copied it to use in another and I get an error 2042 and type
mismatch in the second. I have formatted the target date range in the same
way in both (I thought) so am baffled.

Many TIA'a

Matilda

 

[Bob Phillips]

It works fine as far as I can see. Where do you get the error, and what are
you typing in the inputbox.

BTW, his name is Tom Ogilvy!

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob - of course it is! what was I thinking? I actually have his name
pasted in my code in recognition of his contribution (as I do any time I have
been helped) and it is right there, so must have had a blonde moment! Thanks
for pointing it out.

In put is in dd/mm/yy format, and target range is dd/mm/yyyy but Cdate
handles that. As I said, behaves fine in case 1.
Error is last line I quoted, tooltip over res1 in statement reads error 2042
instead of stating the value of dt match converted to a long.

Same code both applications, same job to do, same data types, formats etc.

Of course it's something stupid, I just can't spot it.

M.

 

[Guest]

Ooops!

Bob, I have discovered the problem, and it is trooooly stoooopid!

I am well aware of the dangers of hardcoding, but the syntax soup required
to reference in excel forces me to use the odd fixed range as an expedient
sometimes.
I simply hadn't checked the reference in the second application, ( somthing
I would have done if I was less weary at this point.!)

Forgive me for wasting this valuable help resource on my own blind stupidity!

Regards,

M

 

[Bob Phillips]

No problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)