I think the problem you are having is those entries are true Excel dates,
not text. If that is the case, then what you see is not really what is in
the cell. You can see that easy enough by selecting one of the cells and
changing its format to General. If you had selected the cell with 01-02-2006
in it, you would now see 38719. That is because Excel stores the date
portion of a date as the number of days since 12/31/1899 (hence, 1 is
January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
probably have to do something like this to accomplish what you want...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
End If
End If
Next
End Sub

Rick


"Xaraam" wrote in message
...
Hey!

Im kind of dummie in VBA. I usually just work with it to create macros.
Ive a important challenge now, that is: using an excel sheet with,
average,
20000 cells, I need to replace a part of the cell content several times
(i.e.
if my cell is 01-02-2006, the €œ02€� has to be replaced by €œMA€�).
Im using the VB6 version and excel 2003.

i've tried this code:
sub replace()
worksheets("book1").select
Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
ReplaceFormat:=False
end sub()

the cells are formatted to date.

i've just noticed that the code does work but sometimes, i.e. if i want to
replace the content "-02-" to "-MA-", in all my attempts it just worked
once.
But if i replace "2006" to "-MA-", it work all the time and the replace
cells are something like this: "01/02/-MA-", from the previous:
"01-02-2006".
Even if i replace "0" to MA on "01-02-2006" the result will be:
"1/2/2-MA--MA-6"

BUt even after all this test and puting the cells on the original values i
try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a
error
message.

Can you help me out here?..thanks in advance.

I think you're at least partially right, Rick. But if Excel is really
looking at the serial number in the cells why can you replace the year
portion of the date?

Anyway, by changing my regional settings date I reproduced the problem.
This is a variation for your macro that preserves leading zeros and is
regional settings agnostic:

Sub Replace02WithMA2()
Dim Cell As Range
Dim CellString As String
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
CellString = Cell.Value
Cell.Value = Replace(CellString, "-02-", "-MD-")
Next
End Sub


--
Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
|I think the problem you are having is those entries are true Excel dates,
| not text. If that is the case, then what you see is not really what is in
| the cell. You can see that easy enough by selecting one of the cells and
| changing its format to General. If you had selected the cell with
01-02-2006
| in it, you would now see 38719. That is because Excel stores the date
| portion of a date as the number of days since 12/31/1899 (hence, 1 is
| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
| probably have to do something like this to accomplish what you want...
|
| Sub Replace02WithMA()
| Dim C As Range
| For Each C In Worksheets("Sheet2").UsedRange
| If IsDate(C.Value) Then
| If Day(C.Value) = 2 Then
| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
| End If
| End If
| Next
| End Sub
|
| Rick
|
|
| "Xaraam" wrote in message
| ...
| Hey!
|
| I'm kind of dummie in VBA. I usually just work with it to create macros.
| I've a important challenge now, that is: using an excel sheet with,
| average,
| 20000 cells, I need to replace a part of the cell content several times
| (i.e.
| if my cell is 01-02-2006, the "02" has to be replaced by "MA").
| I'm using the VB6 version and excel 2003.
|
| i've tried this code:
| sub replace()
| worksheets("book1").select
| Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
| ReplaceFormat:=False
| end sub()
|
| the cells are formatted to date.
|
| i've just noticed that the code does work but sometimes, i.e. if i want
to
| replace the content "-02-" to "-MA-", in all my attempts it just worked
| once.
| But if i replace "2006" to "-MA-", it work all the time and the replace
| cells are something like this: "01/02/-MA-", from the previous:
| "01-02-2006".
| Even if i replace "0" to MA on "01-02-2006" the result will be:
| "1/2/2-MA--MA-6"
|
| BUt even after all this test and puting the cells on the original values
i
| try to do the "-02-" to "-MA-" replacement and nothing happens, nor even
a
| error
| message.
|
| Can you help me out here?..thanks in advance.
|

Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using US
settings.

So his macro uses: Replace(CellString, "-02-", "-MD-")

And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006
with UK settings. Excel, being in US mode, sees this cell as having
2/8/2006 in it so there is no match.

But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does
find that (the first 2 characters in the cell when viewed with US settings)
and changes the cell to -MA-8/2006. Not too helpful.

So, short of changing to US settings, I think the OP should use sub
Replace02WithMA2 I posted.

--
Jim
"Jim Rech" wrote in message
...
|I think you're at least partially right, Rick. But if Excel is really
| looking at the serial number in the cells why can you replace the year
| portion of the date?
|
| Anyway, by changing my regional settings date I reproduced the problem.
| This is a variation for your macro that preserves leading zeros and is
| regional settings agnostic:
|
| Sub Replace02WithMA2()
| Dim Cell As Range
| Dim CellString As String
| For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
| CellString = Cell.Value
| Cell.Value = Replace(CellString, "-02-", "-MD-")
| Next
| End Sub
|
|
| --
| Jim
| "Rick Rothstein (MVP - VB)" wrote in
| message ...
||I think the problem you are having is those entries are true Excel dates,
|| not text. If that is the case, then what you see is not really what is in
|| the cell. You can see that easy enough by selecting one of the cells and
|| changing its format to General. If you had selected the cell with
| 01-02-2006
|| in it, you would now see 38719. That is because Excel stores the date
|| portion of a date as the number of days since 12/31/1899 (hence, 1 is
|| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
|| probably have to do something like this to accomplish what you want...
||
|| Sub Replace02WithMA()
|| Dim C As Range
|| For Each C In Worksheets("Sheet2").UsedRange
|| If IsDate(C.Value) Then
|| If Day(C.Value) = 2 Then
|| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
|| End If
|| End If
|| Next
|| End Sub
||
|| Rick
||
||
|| "Xaraam" wrote in message
|| ...
|| Hey!
||
|| I'm kind of dummie in VBA. I usually just work with it to create
macros.
|| I've a important challenge now, that is: using an excel sheet with,
|| average,
|| 20000 cells, I need to replace a part of the cell content several times
|| (i.e.
|| if my cell is 01-02-2006, the "02" has to be replaced by "MA").
|| I'm using the VB6 version and excel 2003.
||
|| i've tried this code:
|| sub replace()
|| worksheets("book1").select
|| Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
|| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
|| ReplaceFormat:=False
|| end sub()
||
|| the cells are formatted to date.
||
|| i've just noticed that the code does work but sometimes, i.e. if i want
| to
|| replace the content "-02-" to "-MA-", in all my attempts it just worked
|| once.
|| But if i replace "2006" to "-MA-", it work all the time and the replace
|| cells are something like this: "01/02/-MA-", from the previous:
|| "01-02-2006".
|| Even if i replace "0" to MA on "01-02-2006" the result will be:
|| "1/2/2-MA--MA-6"
||
|| BUt even after all this test and puting the cells on the original
values
| i
|| try to do the "-02-" to "-MA-" replacement and nothing happens, nor
even
| a
|| error
|| message.
||
|| Can you help me out here?..thanks in advance.
||
|
|

That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick


"Jim Rech" wrote in message
...
Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using
US
settings.

So his macro uses: Replace(CellString, "-02-", "-MD-")

And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006
with UK settings. Excel, being in US mode, sees this cell as having
2/8/2006 in it so there is no match.

But if you change the macro to Replace(CellString, "2/", "-MD-") Excel
does
find that (the first 2 characters in the cell when viewed with US
settings)
and changes the cell to -MA-8/2006. Not too helpful.

So, short of changing to US settings, I think the OP should use sub
Replace02WithMA2 I posted.

--
Jim
"Jim Rech" wrote in message
...
|I think you're at least partially right, Rick. But if Excel is really
| looking at the serial number in the cells why can you replace the year
| portion of the date?
|
| Anyway, by changing my regional settings date I reproduced the problem.
| This is a variation for your macro that preserves leading zeros and is
| regional settings agnostic:
|
| Sub Replace02WithMA2()
| Dim Cell As Range
| Dim CellString As String
| For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
| CellString = Cell.Value
| Cell.Value = Replace(CellString, "-02-", "-MD-")
| Next
| End Sub
|
|
| --
| Jim
| "Rick Rothstein (MVP - VB)" wrote
in
| message ...
||I think the problem you are having is those entries are true Excel
dates,
|| not text. If that is the case, then what you see is not really what is
in
|| the cell. You can see that easy enough by selecting one of the cells
and
|| changing its format to General. If you had selected the cell with
| 01-02-2006
|| in it, you would now see 38719. That is because Excel stores the date
|| portion of a date as the number of days since 12/31/1899 (hence, 1 is
|| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You
will
|| probably have to do something like this to accomplish what you want...
||
|| Sub Replace02WithMA()
|| Dim C As Range
|| For Each C In Worksheets("Sheet2").UsedRange
|| If IsDate(C.Value) Then
|| If Day(C.Value) = 2 Then
|| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
|| End If
|| End If
|| Next
|| End Sub
||
|| Rick
||
||
|| "Xaraam" wrote in message
|| ...
|| Hey!
||
|| I'm kind of dummie in VBA. I usually just work with it to create
macros.
|| I've a important challenge now, that is: using an excel sheet with,
|| average,
|| 20000 cells, I need to replace a part of the cell content several
times
|| (i.e.
|| if my cell is 01-02-2006, the "02" has to be replaced by "MA").
|| I'm using the VB6 version and excel 2003.
||
|| i've tried this code:
|| sub replace()
|| worksheets("book1").select
|| Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
|| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
|| ReplaceFormat:=False
|| end sub()
||
|| the cells are formatted to date.
||
|| i've just noticed that the code does work but sometimes, i.e. if i
want
| to
|| replace the content "-02-" to "-MA-", in all my attempts it just
worked
|| once.
|| But if i replace "2006" to "-MA-", it work all the time and the
replace
|| cells are something like this: "01/02/-MA-", from the previous:
|| "01-02-2006".
|| Even if i replace "0" to MA on "01-02-2006" the result will be:
|| "1/2/2-MA--MA-6"
||
|| BUt even after all this test and puting the cells on the original
values
| i
|| try to do the "-02-" to "-MA-" replacement and nothing happens, nor
even
| a
|| error
|| message.
||
|| Can you help me out here?..thanks in advance.
||
|
|

Hey Rick!!

it works perfectly!!..thank you a lot!!..the only change i'd to make was:

If IsDate(C.Value) Then
If Month(C.Value) = 2 Then
C.Value = Format(C.Value, "dd-\M\A-yyyy")

coz my format is : "dd-mm-yyyy". all my date is on that format, more used
here in europe.
The code also work without the "\" or even if like this:
C.Value = Format(C.Value, "dd-\MA\-yyyy")

Just clarify me something, what's the porpuse of "usedrange" and with do u
use "c.value" and not just "c"?

"Rick Rothstein (MVP - VB)" wrote:

That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick

The UsedRange is an automatically tracked range by Excel and it covers the
maximum extent of cells ever put into use on the worksheet. Note I said
"ever put into use"... one would have hoped UsedRange covered the maximum
extent of cells currently being used but, alas, it doesn't. If you used a
cell outside of your current ranged of used cells and later on deleted it,
UsedRange still thinks that cell is in use... UsedRange is not supposed to
reset itself smaller. Now I am sure I seen it do that (set itself smaller,
but I have also seen it not do so... I have not been able to divine the rule
behind it). Anyway, using UsedRange, under a normally maintained worksheet,
means the For-Each loop can iterate a smaller-than-the-whole-worksheet range
and still guarantee it will 'hit' every in-use cell on your worksheet.

As for why I use C.Value instead of C... personal preference. A range is an
object and objects usually have a default property. A default property means
if you don't specify it, VB will assume the default property was intended
and automatically reference it. That is what happens when you use C by
itself... VB assumes C.Value. The reason I don't like relying on default
properties is that without specify any property, the object name looks like
a simple variable name making it harder to maintain or modify in the future
(when all the stuff that was fresh in mind when you first created your code
has long since left your memory)... always attaching a property (or method)
reference alerts me that I'm looking at an object, not a variable.

Rick


"Xaraam" wrote in message
...
Hey Rick!!

it works perfectly!!..thank you a lot!!..the only change i'd to make was:

If IsDate(C.Value) Then
If Month(C.Value) = 2 Then
C.Value = Format(C.Value, "dd-\M\A-yyyy")

coz my format is : "dd-mm-yyyy". all my date is on that format, more used
here in europe.
The code also work without the "\" or even if like this:
C.Value = Format(C.Value, "dd-\MA\-yyyy")

Just clarify me something, what's the porpuse of "usedrange" and with do u
use "c.value" and not just "c"?

"Rick Rothstein (MVP - VB)" wrote:

That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of
my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original
comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick

As for what controls the setting/resetting of the UsedRange, it looks like
deleting entire columns and/or rows is the key. See the "To programmatically
reset the used range" section on this webpage...

http://www.contextures.on.ca/xlfaqApp.html

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The UsedRange is an automatically tracked range by Excel and it covers the
maximum extent of cells ever put into use on the worksheet. Note I said
"ever put into use"... one would have hoped UsedRange covered the maximum
extent of cells currently being used but, alas, it doesn't. If you used a
cell outside of your current ranged of used cells and later on deleted it,
UsedRange still thinks that cell is in use... UsedRange is not supposed to
reset itself smaller. Now I am sure I seen it do that (set itself smaller,
but I have also seen it not do so... I have not been able to divine the
rule behind it). Anyway, using UsedRange, under a normally maintained
worksheet, means the For-Each loop can iterate a
smaller-than-the-whole-worksheet range and still guarantee it will 'hit'
every in-use cell on your worksheet.

As for why I use C.Value instead of C... personal preference. A range is
an object and objects usually have a default property. A default property
means if you don't specify it, VB will assume the default property was
intended and automatically reference it. That is what happens when you use
C by itself... VB assumes C.Value. The reason I don't like relying on
default properties is that without specify any property, the object name
looks like a simple variable name making it harder to maintain or modify
in the future (when all the stuff that was fresh in mind when you first
created your code has long since left your memory)... always attaching a
property (or method) reference alerts me that I'm looking at an object,
not a variable.

Rick


"Xaraam" wrote in message
...
Hey Rick!!

it works perfectly!!..thank you a lot!!..the only change i'd to make was:

If IsDate(C.Value) Then
If Month(C.Value) = 2 Then
C.Value = Format(C.Value, "dd-\M\A-yyyy")

coz my format is : "dd-mm-yyyy". all my date is on that format, more used
here in europe.
The code also work without the "\" or even if like this:
C.Value = Format(C.Value, "dd-\MA\-yyyy")

Just clarify me something, what's the porpuse of "usedrange" and with do
u
use "c.value" and not just "c"?

"Rick Rothstein (MVP - VB)" wrote:

That sounds reasonable (thankfully, having worked in the US for my
entire
working career, I never had to deal with international issues in any of
my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original
comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick

woh!..what an explanation!..questions completely vanished!:)

once again thanks for your help and explanations..

"Rick Rothstein (MVP - VB)" wrote:

The UsedRange is an automatically tracked range by Excel and it covers the
maximum extent of cells ever put into use on the worksheet. Note I said
"ever put into use"... one would have hoped UsedRange covered the maximum
extent of cells currently being used but, alas, it doesn't. If you used a
cell outside of your current ranged of used cells and later on deleted it,
UsedRange still thinks that cell is in use... UsedRange is not supposed to
reset itself smaller. Now I am sure I seen it do that (set itself smaller,
but I have also seen it not do so... I have not been able to divine the rule
behind it). Anyway, using UsedRange, under a normally maintained worksheet,
means the For-Each loop can iterate a smaller-than-the-whole-worksheet range
and still guarantee it will 'hit' every in-use cell on your worksheet.

As for why I use C.Value instead of C... personal preference. A range is an
object and objects usually have a default property. A default property means
if you don't specify it, VB will assume the default property was intended
and automatically reference it. That is what happens when you use C by
itself... VB assumes C.Value. The reason I don't like relying on default
properties is that without specify any property, the object name looks like
a simple variable name making it harder to maintain or modify in the future
(when all the stuff that was fresh in mind when you first created your code
has long since left your memory)... always attaching a property (or method)
reference alerts me that I'm looking at an object, not a variable.

Rick


"Xaraam" wrote in message
...
Hey Rick!!

it works perfectly!!..thank you a lot!!..the only change i'd to make was:

If IsDate(C.Value) Then
If Month(C.Value) = 2 Then
C.Value = Format(C.Value, "dd-\M\A-yyyy")

coz my format is : "dd-mm-yyyy". all my date is on that format, more used
here in europe.
The code also work without the "\" or even if like this:
C.Value = Format(C.Value, "dd-\MA\-yyyy")

Just clarify me something, what's the porpuse of "usedrange" and with do u
use "c.value" and not just "c"?

"Rick Rothstein (MVP - VB)" wrote:

That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of
my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original
comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick

it seems like such a nightmare to me

Yes. Stephen Bullen devoted a lot of ink to it in the Excel VBA books he did
with Rob Bovey and John Green. Too bad MS couldn't have used an approach
that didn't make it so much harder for non-US users.

--
Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of my
programs... it seems like such a nightmare to me<g).

As for my macro not having preserved leading zeroes (your original comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick


"Jim Rech" wrote in message
...
Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using
US
settings.

So his macro uses: Replace(CellString, "-02-", "-MD-")

And it hits a cell that 'really' has 08-02-2006 in it, which is
8-Feb-2006
with UK settings. Excel, being in US mode, sees this cell as having
2/8/2006 in it so there is no match.

But if you change the macro to Replace(CellString, "2/", "-MD-") Excel
does
find that (the first 2 characters in the cell when viewed with US
settings)
and changes the cell to -MA-8/2006. Not too helpful.

So, short of changing to US settings, I think the OP should use sub
Replace02WithMA2 I posted.

--
Jim
"Jim Rech" wrote in message
...
|I think you're at least partially right, Rick. But if Excel is really
| looking at the serial number in the cells why can you replace the year
| portion of the date?
|
| Anyway, by changing my regional settings date I reproduced the problem.
| This is a variation for your macro that preserves leading zeros and is
| regional settings agnostic:
|
| Sub Replace02WithMA2()
| Dim Cell As Range
| Dim CellString As String
| For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
| CellString = Cell.Value
| Cell.Value = Replace(CellString, "-02-", "-MD-")
| Next
| End Sub
|
|
| --
| Jim
| "Rick Rothstein (MVP - VB)" wrote
in
| message ...
||I think the problem you are having is those entries are true Excel
dates,
|| not text. If that is the case, then what you see is not really what is
in
|| the cell. You can see that easy enough by selecting one of the cells
and
|| changing its format to General. If you had selected the cell with
| 01-02-2006
|| in it, you would now see 38719. That is because Excel stores the date
|| portion of a date as the number of days since 12/31/1899 (hence, 1 is
|| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You
will
|| probably have to do something like this to accomplish what you want...
||
|| Sub Replace02WithMA()
|| Dim C As Range
|| For Each C In Worksheets("Sheet2").UsedRange
|| If IsDate(C.Value) Then
|| If Day(C.Value) = 2 Then
|| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
|| End If
|| End If
|| Next
|| End Sub
||
|| Rick
||
||
|| "Xaraam" wrote in message
|| ...
|| Hey!
||
|| I'm kind of dummie in VBA. I usually just work with it to create
macros.
|| I've a important challenge now, that is: using an excel sheet with,
|| average,
|| 20000 cells, I need to replace a part of the cell content several
times
|| (i.e.
|| if my cell is 01-02-2006, the "02" has to be replaced by "MA").
|| I'm using the VB6 version and excel 2003.
||
|| i've tried this code:
|| sub replace()
|| worksheets("book1").select
|| Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
|| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
|| ReplaceFormat:=False
|| end sub()
||
|| the cells are formatted to date.
||
|| i've just noticed that the code does work but sometimes, i.e. if i
want
| to
|| replace the content "-02-" to "-MA-", in all my attempts it just
worked
|| once.
|| But if i replace "2006" to "-MA-", it work all the time and the
replace
|| cells are something like this: "01/02/-MA-", from the previous:
|| "01-02-2006".
|| Even if i replace "0" to MA on "01-02-2006" the result will be:
|| "1/2/2-MA--MA-6"
||
|| BUt even after all this test and puting the cells on the original
values
| i
|| try to do the "-02-" to "-MA-" replacement and nothing happens, nor
even
| a
|| error
|| message.
||
|| Can you help me out here?..thanks in advance.
||
|
|

hey Jim!

your code works almost perfectly, probably once i wasn't clear about my date
format you wouldn't guess i was using the "dd-mm-yyyy" format.

the problem on your code is the with the exception of the cell that "mm"
corresponds to "02" it swaps the "dd" value for the "mm" value.
i-e. if the original code is: 09-04-2006, after running the code :
04-09-2008. however this happens only until the "dd" value reachs "12".

thank you very much, anyway, for your code and effort!!

"Jim Rech" wrote:

Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using US
settings.

So his macro uses: Replace(CellString, "-02-", "-MD-")

And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006
with UK settings. Excel, being in US mode, sees this cell as having
2/8/2006 in it so there is no match.

But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does
find that (the first 2 characters in the cell when viewed with US settings)
and changes the cell to -MA-8/2006. Not too helpful.

So, short of changing to US settings, I think the OP should use sub
Replace02WithMA2 I posted.

--
Jim
"Jim Rech" wrote in message
...
|I think you're at least partially right, Rick. But if Excel is really
| looking at the serial number in the cells why can you replace the year
| portion of the date?
|
| Anyway, by changing my regional settings date I reproduced the problem.
| This is a variation for your macro that preserves leading zeros and is
| regional settings agnostic:
|
| Sub Replace02WithMA2()
| Dim Cell As Range
| Dim CellString As String
| For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
| CellString = Cell.Value
| Cell.Value = Replace(CellString, "-02-", "-MD-")
| Next
| End Sub
|
|
| --
| Jim