MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with date formats and searching

  Asked By: Ashley    Date: Aug 27    Category: MS Office    Views: 988

I am using the sub below to copy stock/commodity prices from one
worksheet to another:

Sub CopyPrices()

Dim strdate As String
Dim rCell As Range


strdate = Range("AL5").Value
strdate = Format(strdate, "Short Date")

Range("AL4:AQ9").Find(What:="Last", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range(ActiveCell.Offset(1, -3), ActiveCell.Offset(5, 0)).Select


Set rCell = Selection.Find(What:=CDate(strdate), After:=Range("FN5"),
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext,
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select

End Sub

where worksheet P1 imports the prices via web query and worksheet Db1
is a database that needs to be updated. The format of the date in Db1
is 21/01/2008 (21st Jan 2008). 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. When in the latter format, the sub
fails with "Run-time error '91', Object variable or With Block
variable not set" or selects the wrong date in those cases where the
latter transposed format is also a valid date e.g. 06/07/2008 (6th
July 2008) being confused with 07/06/2008 (7th June 2008). How can I
modify the sub to cope with this problem of variable date formats?



9 Answers Found

Answer #1    Answered By: Salvatore Kelly     Answered On: Aug 27

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.

Answer #2    Answered By: Deloris Harris     Answered On: Aug 27

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.

Answer #3    Answered By: Luisa Fischer     Answered On: Aug 27

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

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

Answer #4    Answered By: Eshe Chalthoum     Answered 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

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,

- 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.

Answer #5    Answered By: Waggoner Fischer     Answered On: Aug 27

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.

Answer #6    Answered By: Davi Costa     Answered On: Aug 27

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

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.

Answer #7    Answered By: Sydney Thompson     Answered On: Aug 27

> 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.

Answer #8    Answered By: Kim Cruz     Answered On: Aug 27

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

Answer #9    Answered By: Adelbert Fischer     Answered On: Aug 27

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.

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