Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and replace | Excel Discussion (Misc queries) | |||
Search and Replace | Excel Worksheet Functions | |||
Search and replace | Excel Worksheet Functions | |||
Help with Search and Replace | Excel Programming | |||
Search and replace | Excel Programming |