You should always try to get dates in yyyy-mm-dd format. This is
unambiguous and should always be translated correctly.

Your comment

> A problem  arises, however, because the
> date  in the web  query table is sometimes
> of the above format, and sometimes in the
> format  01/21/2008.

is a concern, though. Why would the query  return dates in different
formats? You need to sort out the input (query) first, otherwise you are
risking a GIGO situation.

Unfortunately I am unable to follow your suggestion as the web  query
queries a price table hosted on the web on a site over which I have no
control; I have to accept the formatting as it is.

Coming to this very late so ignore and apologies if insensible... :-)

Is there *anything* that indicates a format  for the received date?

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.

Thanks for your input; your comments accurately describe my situation,
however I cannot check the things you suggested at the moment as the
web site date  format is currently not causing any problems. When the
format changes again, I will check things and post again if necessary.

The web  format has recently changed again and I can now give further
information.

The run-time error fails on "rCell.Select" because the value for
strdate is "10/02/2008" (meaning 2nd October 2008) and this value is
not available to be found in column FN because the date  format for
column FN is set to dd/mm/yyyy. In dd/mm/yyyy format  "10/02/2008" was
a Sunday and as this was a non-trading day no such date exists in
column FN, hence the runtime failure. However, when strdate value is
"10/03/2008" (meaning 3rd October 2008) the "rCell.Select" does not
fail but selects 10th March 2008 because this date does exist in
column FN in dd/mm/yyyy format. The problem  is being caused by the
variable date format (dd/mm/yyyy vs mm/dd/yyyy) being returned by the
web query, but I don't really know how I can address this problem
other than by writing a routine to coerce mm/dd/yyyy formatted dates
into dd/mm/yyyy format, which I have no idea how to do at the momemt,
and which might still be problematic when it comes to dates such as
06/07/2008 - it is not obvious from the date alone whether or not it
should be coerced.

> The web  format has recently changed again and I can now give further
> information.

I still find it quite unusual that a web format  would change. This would
require that the web programmer change the programming, for no apparent
reason. I really do suspect that the problem  is at your end. What is the
web site and where are the dates that you put into FN?

> ... because the date  format for
> column FN is set to dd/mm/yyyy.

You don't actually set the date format for a column. You can set the
DISPLAY format, but this doesn't change the way Excel will convert typed
dates to its internal format.

E.g. if I format E13 to mm/dd/yyyy and then enter 12/10 (remember that I'm
in Australia), then this is interpreted as 12th October and the cell
immediately displays as 10/12/2008. E17 has =E13 but is formatted as
dd/mm/yyyy and shows me 12/10/2008.

I.e. the input conversion is done according to the locale setting - not the
cell's format.

However, if I enter 10/13/2008 in E13, Excel will store the value as the
string "10/13/2008" (left justified) and this same string displays in E17
(again left justified). Although this string has not been converted to
internal date format, Excel will still use it as a date in certain
circumstances, and will then treat it as 13th October.

You will need to point us at the source web site and also upload the
offending workbook so we can have a look at them.

The website I am querying is www.barchart.com for various
stock/commodity prices. I cannot upload a sample spreadsheet because I
am restricted from uploading files to the files area

Which particular page are you grabbing the information from on the web  site?

I've just looked at a couple of its pages at random and the dates appear to
be consistently in US format. At the moment, that is 10/09/08 or 10/08/08.

If your computer's locale is set to dd/mm/yy, you are going to need to
import the dates as strings and manipulate them from US date  format to
UK/AU/etc date format.

However, there are also some instances of time instead of date in the
columns I looked at, which you'd need to handle.