View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Rech Jim Rech is offline
external usenet poster
 
Posts: 2,718
Default VBA and Excell - really need you help

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.
|