Replacing dates in VBA

[Adar123]

I am using a replace loop to change certain words.

Dim oRange As Range

 For Each oRange In ActiveSheet.Range("A1:A10000")
    oRange.Replace What:="AssetName", Replacement:="Commodity", MatchCase:=False
 Next

The challenge I have is with dates. The dates are reported as follows: 20/08/2020. It could be any day of the month but I need to replace it with AUG20. For September dates that would be SEP20.

Thank you.

 

[Yongle]

If the cells are formatted as dates and only contain dates then it is simple

Sub ReplaceDates()
    Dim oRange As Range
    Application.ScreenUpdating = False
    For Each oRange In ActiveSheet.Range("A1:A10000")
        With oRange.Offset(, 1)
            .Value = oRange
            .NumberFormat = "mmmyy"
        End With
    Next
End Sub

If that is not what you require, it would help if you posted typical sample data preferably using this tool

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Adar123]

Edited: it worked well! Thank you!

 

[Adar123]

Sorry, a follow up question: I was a bit too excited when saw the result. This appears to change the format of the dates presentation but what it needs to change date into a general text such as "AUG20". The cell should not contain anything else other than AUG20. See B2 and other cells in B column to understand the difference in the result sought. Thanks again.

TestMacroBook.xlsm
	A	B	C
1	Commodity	Pillar	Expiry
2	CBOTCorn	AUG20	14/9/2020
3	CBOTCorn	Sep20	14/9/2020
4	CBOTCorn	Oct20	14/12/2020
5	CBOTCorn	Nov20	14/12/2020
6	CBOTCorn	Dec20	14/12/2020
NewData

 

[Yongle]

In that case, try this

Sub ReplaceDates()
    Dim oRange As Range
    Application.ScreenUpdating = False
    With ActiveSheet.Range("B1:B10000")
            .NumberFormat = "@"
            For Each oRange In .Cells
                oRange.Value = Format(oRange.Value, "mmmyy")
            Next
    End With
End Sub

or if preferred
oRange.Value = uCase(Format(oRange.Value, "mmmyy"))

 

[Adar123]

Thank you! I think this should do the job. Will be running a few tests with this data over coming days.

 

[Adar123]

I've got an interesting problem with this code, which I have not noticed at first. It is changing all dates to MMMYY but for YY it changes all to "20" e.g. 01-July-2021 becomes JUL20. Could this be to do with all years being 2021,2022,etc and the code picking the two first digits?

 

[Yongle]

Below ... dates are in column A ... desired output is in column B

Book1
	A	B
1	04/08/2020	Aug20
2	04/08/2021	Aug21
3	04/08/2022	Aug22
4	04/08/2023	Aug23
5	03/08/2024	Aug24
6	03/08/2025	Aug25
7	03/08/2026	Aug26
8	03/08/2027	Aug27
Sheet6

Sub ReplaceDates()
    Dim oRange As Range
    Application.ScreenUpdating = False
    With ActiveSheet.Range("A1:A8")
            .Offset(, 1).NumberFormat = "@"
            For Each oRange In .Cells
                oRange.Offset(, 1).Value = Format(oRange.Value, "mmmyy")
            Next
    End With
End Sub

 

[Adar123]

I show three columns. A is the data in its original form. Column B is what happened to the original data and column C shows the final output.

Dim bRange As Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

       Application.ScreenUpdating = False
    With ActiveSheet.Range("B2:B" & lastRow)
            .Offset(, 1).NumberFormat = "@"
            For Each bRange In .Cells
                bRange.Offset(, 1).Value = Format(bRange.Value, "mmmyy")
            Next
    
    End With

Ags_20200804.xls
	A	B	C
1	Original	Date	Expiry
2	Sep-20	Sep-20	Sep20
3	Oct-20	Oct-20	Oct20
4	Nov-20	Nov-20	Nov20
5	Dec-20	Dec-20	Dec20
6	Jan-21	Jan-21	Jan20
7	Feb-21	Feb-21	Feb20
8	Mar-21	Mar-21	Mar20
9	Apr-21	Apr-21	Apr20
10	May-21	May-21	May20
11	Jun-21	Jun-21	Jun20
12	Jul-21	Jul-21	Jul20
13	Aug-21	Aug-21	Aug20
14	Sep-21	Sep-21	Sep20
15	Oct-21	Oct-21	Oct20
16	Nov-21	Nov-21	Nov20
17	Dec-21	Dec-21	Dec20
18	Jan-22	Jan-22	Jan20
19	Feb-22	Feb-22	Feb20
20	Mar-22	Mar-22	Mar20
21	Apr-22	Apr-22	Apr20
NewData

 

[Yongle]

Sorry - I do not understand what you want