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…
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
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
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
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.
As you copy this formula down to B25 you get dates from 1 to 25 but from the year and month in B26.