Replace Macro

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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.

 

[Bob Phillips]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

I don't have a guess why xl behaves this way. But it did for me (too).

 

[Sandy Mann]

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 more:

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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

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 more:

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

<<snipped>>

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk