Search Replace in VBA

[pamalpass]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[pamalpass]

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

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[pamalpass]

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

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>