Words like «MonthYear», «Month-Year» are not inserted in cells of column A when using a macro

[moninah]

I have a macro:

Private Sub Worksheet_Change(ByVal Target As range)
  Dim v, cell
  Set Target = Application.Intersect(Target, Me.Columns(1), Me.UsedRange)
  If Target Is Nothing Then Exit Sub
  For Each cell In Target.Cells
    v = cell.Value
    If VarType(v) <> vbDate Then
      Application.EnableEvents = False
      If v Like "???##" Or v Like "???-##" Then cell.Value = Left(v, Len(v) - 2) & "20" & Right(v, 2)
      If VarType(cell.Value) <> vbDate Then cell.Value = Empty
      cell.NumberFormat = "dd/mm/yyyy"
       With Target
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
       End With
      Application.EnableEvents = True
    End If
  Next cell
End Sub

To solve this problem, I use this macro:

I have dates as MonthYear, Month-Year (May20, May-21, etc.) but I need them to be in dd.mm.yyyy format.

For example:
Aug20, needs to become 01.08.2020.
May-19, needs to become 01.05.2019.
Jul18, needs to become 01.07.2018.
Nov-17, needs to become 01.11.2017.
etc.

If you try to enter something other than the MonthYear, Month-Year type into the cells of column A, then nothing is entered into the cells, they should remain empty.

You can try setting your computer's regional settings to dd.mm.yyyy. Then set the format for all cells in column A (Cell format → Date → default should be dd.mm.yyyy). Insert my macro into an Excel sheet. Try doing the following with my macro: Copy from any cell (CTRL+C) the word «may20» and paste (CTRL+V) into any cell in column A. You will only be able to paste once.

 

[rollis13]

Not sure if I correctly understood your request but all I did was change this line of code to have the four dates in your example to convert correctly:

cell.NumberFormat = "dd/mm/yyyy"

to:
cell.NumberFormat = "dd.mm.yyyy"

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Disable buffer clearing at a given cell format (NumberFormat = "m/d/yyyy")
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

As near as I can figure, the problem he is trying to solve is that if he does a copy and paste on the worksheet, the Change event is triggered, and when the Sub is called it clears the clipboard. Therefore he cannot paste the copied cell more than once.