Search Replace in VBA
 

I tend to record all my actions in a macro and then hack around t
create what i really need.

This project is to download data from a web site using the web quer
and then to create a pivot table of the data.

Problem 1 occurred when trying to change text from the form 01.02.200
to date format. I assumed this would be a recognisable date format bu
i couldnt seem to make Excel understand it until i replaced the '.
with '/'. So i used a replace all for '.' with '/' no problem. Howeve
if i recorded this as a macro and replayed it then it made some errors
occasionally some date would be reversed and appear as 02/01/2006.

Anyone know why it reversed the month and day?

ill post problem 2 another time
Pau

--
pamalpas
-----------------------------------------------------------------------
pamalpass's Profile: http://www.excelforum.com/member.php...fo&userid=3195
View this thread: http://www.excelforum.com/showthread.php?threadid=51672

Search Replace in VBA
 

When VBA touches it, it sees it as a US style date (mm/dd/yyyy).

You will probably need to process each cell individually

Dim cell as Range, sDay as String, sMon as String, sYear as String
Dim dt as Date
For each cell in Range(cells(2,5),cells(2,5).End(xldown))
sDay = left(cell.Text,2)
sMon = Mid(cell.Text,4,2)
sYear = Mid(cell.Text,7,4)
dt = DateSerial(clng(sYear),clng(sMon),clng(sDay))
Cell.Value = dt
Cell.Numberformat = "dd.mm.yyyy"
Next

a more compact alternative would be

Dim cell as Range
Dim dt as Date
For each cell in Range(cells(2,5),cells(2,5).End(xldown))
dt = cdate(Replace(cell.Text,".","\"))
Cell.Value = dt
Cell.Numberformat = "dd.mm.yyyy"
Next

--
Regards,
Tom Ogilvy

"pamalpass" wrote in
message ...

I tend to record all my actions in a macro and then hack around to
create what i really need.

This project is to download data from a web site using the web query
and then to create a pivot table of the data.

Problem 1 occurred when trying to change text from the form 01.02.2006
to date format. I assumed this would be a recognisable date format but
i couldnt seem to make Excel understand it until i replaced the '.'
with '/'. So i used a replace all for '.' with '/' no problem. However
if i recorded this as a macro and replayed it then it made some errors.
occasionally some date would be reversed and appear as 02/01/2006.

Anyone know why it reversed the month and day?

ill post problem 2 another time
Paul


--
pamalpass
------------------------------------------------------------------------
pamalpass's Profile:
http://www.excelforum.com/member.php...o&userid=31952
View this thread: http://www.excelforum.com/showthread...hreadid=516726

Search Replace in VBA
 

Thanks for the quick response. I tried both versions but i get an erro
13 type mismatch
on the line
dt = DateSerial(clng(sYear),clng(sMon),clng(sDay))


and the same error on the second example with the 'dt=' line

Pau

--
pamalpas
-----------------------------------------------------------------------
pamalpass's Profile: http://www.excelforum.com/member.php...fo&userid=3195
View this thread: http://www.excelforum.com/showthread.php?threadid=51672

Search Replace in VBA
 

The first worked fine for me. The second I had the slash backwards. Once I
changed that, it worked fine.

Can't explain your inability to use it. Sorry.

--
Regards,
Tom Ogilvy


"pamalpass" wrote
in message ...

Thanks for the quick response. I tried both versions but i get an error
13 type mismatch
on the line
dt = DateSerial(clng(sYear),clng(sMon),clng(sDay))


and the same error on the second example with the 'dt=' line

Paul


--
pamalpass
------------------------------------------------------------------------
pamalpass's Profile:
http://www.excelforum.com/member.php...o&userid=31952
View this thread: http://www.excelforum.com/showthread...hreadid=516726

Search Replace in VBA
 

my mistake. all working fine. i had missed another step in formtatting
my source data. It had blank lines in between the date fields.

i can even automatically create the pivot table now. excellent.

minor issue is that i use the date the report was created (today()) as
a comparison to wrok out the age. However i want this to be a fixed
value and not constantly compared to the 'day' the spreadsheet is
opend.

is there a way of easily doing this?
Paul


--
pamalpass
------------------------------------------------------------------------
pamalpass's Profile: http://www.excelforum.com/member.php...o&userid=31952
View this thread: http://www.excelforum.com/showthread...hreadid=516726

Search Replace in VBA
 

Enter it with Ctrl+Semicolon rather than use a formula

or

Enter it with code
Cells(3,5).Value = Date

or

enter

=today()

then select the cell and do edit=Copy, then Edit=PasteSpecial and select
values.

--
Regards,
Tom Ogilvy

"pamalpass" wrote
in message ...

my mistake. all working fine. i had missed another step in formtatting
my source data. It had blank lines in between the date fields.

i can even automatically create the pivot table now. excellent.

minor issue is that i use the date the report was created (today()) as
a comparison to wrok out the age. However i want this to be a fixed
value and not constantly compared to the 'day' the spreadsheet is
opend.

is there a way of easily doing this?
Paul


--
pamalpass
------------------------------------------------------------------------
pamalpass's Profile:
http://www.excelforum.com/member.php...o&userid=31952
View this thread: http://www.excelforum.com/showthread...hreadid=516726