CharlesB1611
New Member
- Joined
- Sep 8, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hello,
Probably an easy one but I am somewhat new to vba and have this special project: I have a three column sheet that contains various strings in each cell of column C. Some of the columns have a date value of 09/04/2022 at the very beginning of the string, and some have a date field at the end of the string similar to "Birthdate: 04/16/1982" (quotes not included). I have a macro that goes through each line and replaces 09/ with DATE 09/. I only want it to change the date that is at the beginning not any other. My code is simple but finds the birthdate date and changes it as well. Any ideas? Here is my code:
Sub Clean_EJ01()
Worksheets("ConvertedEJ").Activate
With ActiveSheet
'Replace or remove new values with old values
Worksheets("ConvertedEJ").Columns("C").Replace What:="]", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="[PLU# : ", Replacement:="PLU#", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="01/", Replacement:="DATE 01/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="02/", Replacement:="DATE 02/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="03/", Replacement:="DATE 03/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="04/", Replacement:="DATE 04/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="05/", Replacement:="DATE 05/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="06/", Replacement:="DATE 06/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="07/", Replacement:="DATE 07/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="08/", Replacement:="DATE 08/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="09/", Replacement:="DATE 09/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="10/", Replacement:="DATE 10/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="11/", Replacement:="DATE 11/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="12/", Replacement:="DATE 12/", SearchOrder:=xlByColumns, MatchCase:=False
End With
End Sub
Probably an easy one but I am somewhat new to vba and have this special project: I have a three column sheet that contains various strings in each cell of column C. Some of the columns have a date value of 09/04/2022 at the very beginning of the string, and some have a date field at the end of the string similar to "Birthdate: 04/16/1982" (quotes not included). I have a macro that goes through each line and replaces 09/ with DATE 09/. I only want it to change the date that is at the beginning not any other. My code is simple but finds the birthdate date and changes it as well. Any ideas? Here is my code:
Sub Clean_EJ01()
Worksheets("ConvertedEJ").Activate
With ActiveSheet
'Replace or remove new values with old values
Worksheets("ConvertedEJ").Columns("C").Replace What:="]", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="[PLU# : ", Replacement:="PLU#", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="01/", Replacement:="DATE 01/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="02/", Replacement:="DATE 02/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="03/", Replacement:="DATE 03/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="04/", Replacement:="DATE 04/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="05/", Replacement:="DATE 05/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="06/", Replacement:="DATE 06/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="07/", Replacement:="DATE 07/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="08/", Replacement:="DATE 08/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="09/", Replacement:="DATE 09/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="10/", Replacement:="DATE 10/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="11/", Replacement:="DATE 11/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="12/", Replacement:="DATE 12/", SearchOrder:=xlByColumns, MatchCase:=False
End With
End Sub