Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Ashley Smith   on Aug 27 In MS Office Category.

  
Question Answered By: Eshe Chalthoum    on Aug 27

You haven't said which statement actually fails with the run-time error.
Where exactly is the failure and what is the exact value of any parameters
when it fails?

If it's failing on "rCell.Select", then what is in strdate in the "Set
rCell" statement? Is that value definitely available to be found in column
FN?

On the other hand, it would be quite unusual for a single source of
information to randomly give you US and non-US date  formats. Check the
contents of strdate after "strdate = Range("AL5").Value" to see if the
format really is being randomised and also the contents of column FN.
Better still, check at the source.

There is a complication ... Excel will (most infuriatingly) "help" you with
date conversion. E.g. if your computer is set to dd/mm/yy but it gets a
text string "12/13/08" it'll convert it to 13/12/08. But if you get
"12/11/08" it'll keep it as "12/11/08". There is absolutely no
justification for doing this and I wish they'd stop.

My suspicion (but that's all it is) ...

- Your computer is set to dd/mm/yy (Australia, UK, etc)

- The site is delivering you US format  addresses (mm/dd/yy)

- your Format(strdate, "Short Date") and/or your CDate is misinterpreting
12/11/08 types of dates and correctly interpreting 12/13/08 types of dates,
and/or

- your dates in column FN are not behaving consistently (e.g. 12/13/08 might
be being stored as a string while 12/11/08 is stored as a date)

- the find is failing, and therefore not setting rCell

- the sub fails when you try to rCell.Select because the find failed

Check column FN first. Dates that are being interpreted as dates will be
right justified, while dates that are treated as strings will be left
justified. Your search will not find dates stored as strings.

Then treat strdate with suspicion. Trace it through and look at what goes
into it. (Note that the "Format" line should be unnecessary. Comment it
out and see if anything changes.)

Failing all that, you might need to upload an example spreadsheet.

Share: 

 

This Question has 8 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Problem with date formats and searching Or get search suggestion and latest updates.


Tagged: