Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
Using XL97.
If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
First, my windows regional settings for time is:
hh:mm:ss tt (hours, minutes, seconds, AM/PM) When I did edit|replace manually (in xl2003), the space before the AM/PM was changed. But when I looked at the formulabar, I saw times like: 06:12:20 PM even though the cell was formatted: hh:mm:ss and showed: 18:12:20 But if I changed my windows regional settings (via control panel) to not display the AM/PM (I used a time format of: HH:mm:ss and HH means 24 hour time display), then the value shown in the formulabar was 18:12:20 and there was no space to get rid of. So I think you have a couple of choices. You could do all the changes, then change the "AM" or "PM" to " AM" or " PM". Or if your data is all times, you could format the range as General, do the change and then change it back to a time format. ====== Just an aside... Try this: with activecell .numberformat = "General" .value = now msgbox .value & vblf & .value2 end with You'll see a difference. Sandy Mann wrote: Using XL97. If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
That's odd Sandy.I can confirm it also happens in 2003 and 2007.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Using XL97. If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
Thank you for confirming it Bob. Just when I begin to think that I am
getting a handle on this thing............. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Bob Phillips" wrote in message ... That's odd Sandy.I can confirm it also happens in 2003 and 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Using XL97. If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
I don't have a guess why xl behaves this way. But it did for me (too).
Sandy Mann wrote: Thank you Dave, I never thought of checking the settings in the comtrol panel but when I did, I found that they were already set to HH:mm:ss. I got around the problem by first checking if the data in the cell was numeric which, to my mind, is even more curious because if VBA can see that it is numeric then why does it treat it like text? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... First, my windows regional settings for time is: hh:mm:ss tt (hours, minutes, seconds, AM/PM) When I did edit|replace manually (in xl2003), the space before the AM/PM was changed. But when I looked at the formulabar, I saw times like: 06:12:20 PM even though the cell was formatted: hh:mm:ss and showed: 18:12:20 But if I changed my windows regional settings (via control panel) to not display the AM/PM (I used a time format of: HH:mm:ss and HH means 24 hour time display), then the value shown in the formulabar was 18:12:20 and there was no space to get rid of. So I think you have a couple of choices. You could do all the changes, then change the "AM" or "PM" to " AM" or " PM". Or if your data is all times, you could format the range as General, do the change and then change it back to a time format. ====== Just an aside... Try this: with activecell .numberformat = "General" .value = now msgbox .value & vblf & .value2 end with You'll see a difference. Sandy Mann wrote: Using XL97. If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
Thanks again Dave,
If you don't have a guess then there is little hope for me. I did some further testing and it has only served to confuse me mo Sub Macro1() Range("A1").Select If Not IsNumeric(A1) Then Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End If End Sub Which is the method that I said I used to get around the problem, (although my real macro is slightly more complicated you understand <g). On the surface it looks logical - well it did to me until I read Help: +++++++++++++++++++++++++++++++++++++++ Remarks IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False. IsNumeric returns False if expression is a date expression +++++++++++++++++++++++++++++++++++++++ and for *date expression:* ***************************************** Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 - December 31, 9999. Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899. ***************************************** So as a time is really a date and date expressions retuns FALSE then I should have been able to use: If IsNumeric(A1) Then and because this *should mean* If FALSE it should leave the time alone but no, If IsNumeric(A1) changes the time and If Not IsNumeric(A1) leaves it alone! The only way that I can resolve this contradiction in my mind is to say that formatting a cell after an entry does not change the contents of the cell *unless* the format is a Time where upon it changes to a suedo-number which is not recognised as text by Replace, (or XL) but is recognised by VBA. Even more strange, with:. Sub Macro1() Range("A1").Select Cells.Replace What:="A", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub This removes the "A" from AM but if a date of 1/4/07, (April 1 2007) is entered and formatted as d mmmm yyyy then it leaves it alone. But: Sub Macro1() Range("A1").Select Cells.Replace What:="1", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub Causes the 1 April 2007 displayed to become 4/2007 This is obviously far beyond me! -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... I don't have a guess why xl behaves this way. But it did for me (too). Sandy Mann wrote: Thank you Dave, I never thought of checking the settings in the comtrol panel but when I did, I found that they were already set to HH:mm:ss. I got around the problem by first checking if the data in the cell was numeric which, to my mind, is even more curious because if VBA can see that it is numeric then why does it treat it like text? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... First, my windows regional settings for time is: hh:mm:ss tt (hours, minutes, seconds, AM/PM) When I did edit|replace manually (in xl2003), the space before the AM/PM was changed. But when I looked at the formulabar, I saw times like: 06:12:20 PM even though the cell was formatted: hh:mm:ss and showed: 18:12:20 But if I changed my windows regional settings (via control panel) to not display the AM/PM (I used a time format of: HH:mm:ss and HH means 24 hour time display), then the value shown in the formulabar was 18:12:20 and there was no space to get rid of. So I think you have a couple of choices. You could do all the changes, then change the "AM" or "PM" to " AM" or " PM". Or if your data is all times, you could format the range as General, do the change and then change it back to a time format. ====== Just an aside... Try this: with activecell .numberformat = "General" .value = now msgbox .value & vblf & .value2 end with You'll see a difference. Sandy Mann wrote: Using XL97. If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to select A1 and replace a space with nothing, XL rightly tells me that if cannot find any matching data to replace. However, if I then run the macro that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it removes the space between the 00 and the AM as if the entry had been 8:00:00 AM Two questions: 1. I thought that formatting was just a mask over the *real* data in the cell which is what is displayed in General format. If that is the case then A1 would have held 0.33333333333333, so why does the VBA Replace find a space? I suppose that the *Replace* in VBA is not the same *Replace* as the one on the Edit menu but surely the data is 0.33333333333333 not a text 8:00:00 AM. Sub Macro1() Range("A1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub (I know that if I change it to LookAt:=xlWhole then it will not replace the space but I can't understand how it happens in VBA but not manually.) 2. Does this just happen in XL97 or all versions? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
Dates and times are funny <vbg.
You could also use application.isnumber(range("a1").value) and you'll see a True (with =now() in A1). Or you can use isnumeric(range("a1").value2) to return True. I'm not sure exactly what you're doing, but maybe using specialcells to limit the range to text constants. Times and dates were "deselected" when I did it manually and in code. Option Explicit Sub testme() Dim myRng As Range With ActiveSheet.UsedRange Set myRng = Nothing On Error Resume Next Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo 0 If myRng Is Nothing Then 'nothing to change Else 'do the replace against myrng myRng.Replace what:="A", replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End If End With End Sub Sandy Mann wrote: Thanks again Dave, If you don't have a guess then there is little hope for me. I did some further testing and it has only served to confuse me mo Sub Macro1() Range("A1").Select If Not IsNumeric(A1) Then Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End If End Sub Which is the method that I said I used to get around the problem, (although my real macro is slightly more complicated you understand <g). On the surface it looks logical - well it did to me until I read Help: +++++++++++++++++++++++++++++++++++++++ Remarks IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False. IsNumeric returns False if expression is a date expression +++++++++++++++++++++++++++++++++++++++ and for *date expression:* ***************************************** Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 - December 31, 9999. Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899. ***************************************** So as a time is really a date and date expressions retuns FALSE then I should have been able to use: If IsNumeric(A1) Then and because this *should mean* If FALSE it should leave the time alone but no, If IsNumeric(A1) changes the time and If Not IsNumeric(A1) leaves it alone! The only way that I can resolve this contradiction in my mind is to say that formatting a cell after an entry does not change the contents of the cell *unless* the format is a Time where upon it changes to a suedo-number which is not recognised as text by Replace, (or XL) but is recognised by VBA. Even more strange, with:. Sub Macro1() Range("A1").Select Cells.Replace What:="A", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub This removes the "A" from AM but if a date of 1/4/07, (April 1 2007) is entered and formatted as d mmmm yyyy then it leaves it alone. But: Sub Macro1() Range("A1").Select Cells.Replace What:="1", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub Causes the 1 April 2007 displayed to become 4/2007 This is obviously far beyond me! -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... I don't have a guess why xl behaves this way. But it did for me (too). <<snipped -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Macro
Thank you Dave, Special cells is a good idea I will try that.
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... Dates and times are funny <vbg. You could also use application.isnumber(range("a1").value) and you'll see a True (with =now() in A1). Or you can use isnumeric(range("a1").value2) to return True. I'm not sure exactly what you're doing, but maybe using specialcells to limit the range to text constants. Times and dates were "deselected" when I did it manually and in code. Option Explicit Sub testme() Dim myRng As Range With ActiveSheet.UsedRange Set myRng = Nothing On Error Resume Next Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo 0 If myRng Is Nothing Then 'nothing to change Else 'do the replace against myrng myRng.Replace what:="A", replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End If End With End Sub Sandy Mann wrote: Thanks again Dave, If you don't have a guess then there is little hope for me. I did some further testing and it has only served to confuse me mo Sub Macro1() Range("A1").Select If Not IsNumeric(A1) Then Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End If End Sub Which is the method that I said I used to get around the problem, (although my real macro is slightly more complicated you understand <g). On the surface it looks logical - well it did to me until I read Help: +++++++++++++++++++++++++++++++++++++++ Remarks IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False. IsNumeric returns False if expression is a date expression +++++++++++++++++++++++++++++++++++++++ and for *date expression:* ***************************************** Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 - December 31, 9999. Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899. ***************************************** So as a time is really a date and date expressions retuns FALSE then I should have been able to use: If IsNumeric(A1) Then and because this *should mean* If FALSE it should leave the time alone but no, If IsNumeric(A1) changes the time and If Not IsNumeric(A1) leaves it alone! The only way that I can resolve this contradiction in my mind is to say that formatting a cell after an entry does not change the contents of the cell *unless* the format is a Time where upon it changes to a suedo-number which is not recognised as text by Replace, (or XL) but is recognised by VBA. Even more strange, with:. Sub Macro1() Range("A1").Select Cells.Replace What:="A", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub This removes the "A" from AM but if a date of 1/4/07, (April 1 2007) is entered and formatted as d mmmm yyyy then it leaves it alone. But: Sub Macro1() Range("A1").Select Cells.Replace What:="1", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False End Sub Causes the 1 April 2007 displayed to become 4/2007 This is obviously far beyond me! -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave Peterson" wrote in message ... I don't have a guess why xl behaves this way. But it did for me (too). <<snipped -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Vlookup with a macro | Excel Discussion (Misc queries) | |||
Macro to Find & Replace | Excel Worksheet Functions | |||
find&replace macro | Excel Worksheet Functions | |||
replace macro? | Excel Worksheet Functions | |||
Find & Replace in VB macro | Excel Discussion (Misc queries) |