why did the macro stopped working?

M

Martyn

I used to activate the below macro via a control button for changing column
E data format from "dd.mm.yyyy" to "dd/mm/yyyy" of a worksheet. But it ain't
working recently. Any ideas why? (p.s. : recently u/g to WinXL from Win98).
=================
Sub Macro2()
Columns("E:E").Replace _
What:=".", _
Replacement:="/", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub
=================
TIA
Martyn W.
 
F

Frank Kabel

Hi
if you have real dates in this column you should change the number
format. try
Columns("E:E").numberformat = "DD/MM/YYYY"
 
M

Martyn

Hi Frank,
Unfortunately that didn't effect anything. I checked the original format of
data in my column E:E and confirm that they are in "gg/aa/yyyy" format
(correct date format for my friend: format in Turkish language). And I am a
bit confused why I should be using "numberformat" in code?
TIA
 
F

Frank Kabel

Hi
a date format is a number format. So if you have stored your dates as
real date values you only change the format of the cell. Trying to
replace the delimiters won't work as they're just a format (and not
really within the cell).
Also you don't have to change the format string within the code to your
regional settings (VBA expects english indetifiers -> I have to use
them also in my German Excel)

so could you check if the values in your column are real dates. E.g.
enter the worksheetfunction
=ISNUMBER(E1)
this should return TRUE
 
M

Martyn

Hi Frank,
Yes the values in my column E are real dates.
I've checked the worksheetfunctin
=ISNUMBER(E1)
=ISNUMBER(E2)
....
...
=ISNUMBER(E100) etc

and all gives me the results TRUE.
Now what can I do?
Regards
 
F

Frank Kabel

Hi
then simply changing the format with a procedure like
sub foo()
Columns("E:E").numberformat = "DD/MM/YYYY"
end sub

should change your format of this column
 
M

Martyn

Hi Frank, Actually "nothing" changes when I execute the macro!. No error
messages, but
the E column "." symbols are not replaced with "/".
 
F

Frank Kabel

Hi
o.k.
mail me your file and describe in your mail what exactly is not working
and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de
 
M

Martyn

Hi,
I've sent the file to your email with detailed info...
TIA
Martyn

Frank Kabel said:
Hi
o.k.
mail me your file and describe in your mail what exactly is not working
and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Yes I am sure.
Click to expand...
Click to expand...
 
Top