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]

The code in post#8 achieves ...
... what you requested in post#1
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

... plus what you requested in post#4
change date into a general text such as "AUG20"

 

[Adar123]

Sorry, the data that I attempt to transform is in column A, the result I am getting when running the proposed code is in Column C.

As you can see from the table, for some reason when the code is run against cell A21, the result is in C21 = Apr20, instead of Apr22.

 

[Yongle]

But that is not what you requested. And that is why I am confused.
Please explain again so that there is no confusion

what is the format of the original data?
- is it a number formatted as a date
- OR is it text?
- if text what does 15 December 2021 look like in the original data?

Is the required result DEC21 ?

 

[Adar123]

My apologies, I should have used XL2BB in the first place. Please refer to the extract. Column A is data in its original form. Column B is the result sought.

The original data is formatted as Custom format "mmm-yy". The result is in General format.

DataSetTest.csv
	A	B
1	Contract
2	Sep-20	SEP20
3	Oct-20	OCT20
DataSetTest

Did I provide sufficient and clear information?

 

[Yongle]

EDIT : XL2BB(below) is not showing column A correctly. It looks like this in the actual worksheet

-----------------------------------------------------------------------------------------------------------
As you can see ...
- formula in Column C returns what you want (not used in VBA)
- VBA returns identical values in column D (the exact same VBA as in post#8)

Hopefully this resolves your problem

Book1
	A	B	C	D
1	Contract	expect	formula	vba
2	01/09/2020	SEP20	SEP20	SEP20
3	01/10/2020	OCT20	OCT20	OCT20
Order Sheet

Cell Formulas
Range	Formula
C2:C3	C2	=UPPER(TEXT(A2,"MMMYY"))

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

 

[Adar123]

I think that I found the issue. For some reason during the copy-pasting exercise of data from the source workbook to the workbook I am doing all the changes to the data in, the data which is originally recognized as "Custom" becomes recognized as "text" by the Excel. As a result of this, it generated all dates as calendar 2020.

The solution is to point to another column in the dataset as a reference which also contains dates but those are recognized as "dates" (instead of "Custom") by the Excel. Then this code works just fine.

 

[Yongle]

good - that explains everything

 

[Adar123]

Thanks for your patience!

Btw, I realized that for some of the data, the result needs to be in a form of the next month:
E.g. date is 17/08/2020 and using this code, I currently replace values with AUG20.

How do I instruct to replace all August 2020 dates with "SEP20", December 2020 with "JAN21", etc.?

 

[Yongle]

does etc imply this pattern ?
AUG20 =SEP20
DEC20 = JAN21
APR21 = MAY21
AUG21 = SEP21
etc

 

[Adar123]

Yes, that's correct.

I was thinking about adding another line to do a simple replace as a solution...but wondering if there is something more neat than that.