VBA/ EXCEL FORMUL How to copy date with value by macro

[Palucci]

I have a part of a macro that changes the year date range for me, with this macro I would like to automatically change the range every month when I run the macro. For example, in December he wants December 2020-12: 2021-12.

With wbMe.Worksheets("input_6")
   .Range("X26").Delete xlShiftToLeft
   .Range("AJ26").Value = DateAdd("m", 1, .Range("AI26").Value)
End With
=INDEX($A$1:$AE$13,2,match(X26,1:1,0))

But when I do that with code it gets something like this and formula looks like : =INDEX($A$1:$AE$13,2,match(#ADR!,1:1,0))

How can resolve this ?

 

[offthelip]

Your code is incomplete , and you appear to be deleting the X26 that you are trying to use in a MATCH function so it is no surprise it shows up an error. What are you trying to do?? It is usually easier and faster in VBA to avoid deleting cells, columns and rows specially avoid delete and shift. It is very easiy to just overwrite the values. Your code doesn't seem to do what you titles was asking for. Tell us what dates you wnat in what columns and where to pick up the start date and it can be done in VBa very quickly and easily see the code below:

Sub test()
yr = Range("a1:a1")
If IsNumeric(yr) Then
For i = 1 To 12
 Range(Cells(2, i), Cells(2, i)) = DateSerial(yr, i, 1)
Next i
Else
 MsgBox ("enter year number in cells A1")
End If
End Sub

Run the code in blank workbook and enter the year you want in cell A1 then run the code

 

[Palucci]

I have a year range of dates in the table used for charts. So e.g. 2020-11 - 200-11. A1: b13 is the range from which it pulls data into the table. X26 is a date as a reference so I pull data from a range to my table. The macro would have to move the date range with values every month so that, for example, as in the forest today, it would be the December range, i.e. 2020-12: 2012-12.

 

[offthelip]

Your exact requirements are still not clear,: So e.g. 2020-11 - 200-11.????? A1: b13 ????? 2 columns of dates ??? X26 is a date as a reference ??? is this the first date you want?? the December range, i.e. 2020-12: 2012-12. ?? What back 8 years???


However this code will write dates in A1 to A12 these dates will be the first of the month. starting with the month of the date that is given in Cell X26.
Hopefully this is something near what you want .

Sub test()
refdate = Range("x26:x26")
If IsDate(refdate) Then
    mon1 = Month(refdate) - 1
    yr1 = Year(refdate)
    For i = 1 To 12
        Range(Cells(i, 1), Cells(i, 1)) = DateSerial(yr1, mon1 + i, 1)
        If mon1 + i = 12 Then
            mon1 = 0
            yr1 = yr1 + 1
        End If
    Next i
Else
     MsgBox ("enter a date  in cells X26")
End If
End Sub