Macro changes the format of the cells.

[Tamires Lustosa]

Hi everyone
I have no clue about programming in VBA but I am trying to record a macro to delete the characters "*" and ">" from my table.
The table format is "mm:ss.0" and the value is for example 00:01:46 when I select the cell.
But when I run my macro, it changes to 12:01:46...
It changes completely the value of Collum A (that converts the time to the total amount of seconds), as shown in the picture.

This can sound stupid but why is this happening?
Remember that I didnt write the code, I pressed in "record macro"
Here is the code:
Sub Delete_Character()
'
' Delete_Character Macro
' Delete * < >
'
' Keyboard Shortcut: Ctrl+d
'
Columns("D:D").Select
Selection.Replace What:=" *", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" >", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D3").Select
End Sub

Thank you so much!

 

[FormR]

Hi, welcome to the forum!

When trying to replace the * character, you need to escape its special meaning as a wildcard by preceding it with a ~ character.

Why not doing this has the specific effect you are seeing I'm not sure, but try this version of your macro that is tidied up a little and uses the escape character.

Sub Delete_Character()
With Columns("D:D")
    .Replace What:=" ~*", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    .Replace What:=" >", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End With
End Sub

 

[Tamires Lustosa]

Hi Thank you so much! It works now.
Can you just help me of applying this same macro for 2 more other sheets in the same workbook?
I looked on Google but seems complex.

Thank you VERY much

 

[FormR]

Can you just help me of applying this same macro for 2 more other sheets in the same workbook?

Hi, you can try like this.

Sub Delete_Character()
Dim sh As Variant
For Each sh In Array("Sheet1", "Sheet2", "Sheet3") ' Change to the list of sheet names where you want to replacement to happen
    With Sheets(sh).Columns("D:D")
        .Replace What:=" ~*", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=" >", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    End With
Next sh
End Sub

 

[Tamires Lustosa]

It works!!

Thank you so much again!!!! Was really helpful