Copy and replace part of date from field A to B

I need a simple code that takes date from fixed (always the same) field (B 26 on example below) and copy only the MM.YYYY (from date 19.09.2020 I need the copy only of 09.2020) and this MM.YYYY insert into the date field B2 and replace the old MM.YYYY. Day DD (01 from example) from B2 should stay.

Example: B26 -> I need copy of 09.2020 B2 -> 01.08.2020 needs to be raplaced by 09.2020, result should be 01.09.2020. B3…B25 -> all lines from B3 to B25 should bet he same, B3 should be 02.09.2020, B4 should be 03.09.2020…

enter image description here

I have an issue probably with wrong format of date.

Range("B26").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "8/1/2020"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B25"), Type:=xlFillDefault
Range("B2:B25").Select

2 answers

  • answered 2020-06-02 10:06 Pᴇʜ

    Loop through each cell that needs to be replaced and use the DateSerial function to create a date that consists of year and month of the B26 and the day of the current cell in the loop.

    Dim Cell As Range
    For Each Cell In Range("D2:D25")
        Cell.Value = DateSerial(Year(Range("B26").Value), Month(Range("B26").Value), Day(Cell.Value))
    Next Cell
    

  • answered 2020-06-02 10:14 Variatus

    The basic worksheet function you need for this the DATE() function with the syntax =DATE([Year],[Month],[Day]). VBA has an equivalent called DateSerial([Year],[Month],[Day]). Therefore

    [B2] = DATE(Year(B26),Month(B26)+1,1)

    or, in VBA,

    With Cells(26, "B")
        Cells(2, "B").Value = DateSerial(Year(.Value), Month(.Value) + 1, 1)
    End With
    

    or, alternatively, using the DateAdd function

    With Cells(2, "B")
        .Value = DateAdd("m", 1, .Value)
    End With
    

    The latter just adds one month to the date already existing in the cell. Expanding on the idea of using a worksheet function, you can crease a list of numbers from 1 to 24 in B2:B25 using the function =ROW()-1. You might integrate that into the function first above given.

    [B2] = DATE(Year(B$26),Month(B$26)+1,ROW()-1)

    As you copy this formula down to B25 you get dates from 1 to 25 but from the year and month in B26.