[VBA] Roll back through months in a loop

[RockandGrohl]

Hi, really not sure how to make the title sound like what I want to do.

I have a user-set date which is input as Mmm-YY (Oct-21 for instance) - when placed in a cell it's listed as 01/10/2021 which is absolutely fine, the format is set as "Oct-21" so it's perfect.

I'd like to put the previous 5 months in cells to the left. Say Oct-21 is in cell L4, I'd like a macro to just but Sep-21 in K4, Aug-21 in J4 etc until it gets to May-21 where it inputs that and stops.


This is the sort of thing I could do with my eyes closed but after an extended break from VBA, I simply can't remember Sad face.


EDIT: I'm a long way off testing, would this work bychance?

RepDate = InputBox("Please enter date in MM-YY format", "Enter Date")
Range("L4").Value = Format(RepDate, "Mmm-YY")

Range("K4").Activate
x = -1
Do Until Cells(ActiveCell.Column) = 7
    ActiveCell.Value = Format(DateAdd("m", x, RepDate), "Mmm-YY")
    x = x - 1
ActiveCell.Offset(0, -1).Activate
Loop

I need RepDate to take 10-21 and convert it to 01/10/2021

 

[smozgur]

I'm a long way off testing, would this work bychance?

Very close.

My approach will be something like the following, still using the DateAdd function as you did. However, the Format function in VBA won't help you display the cell value as you need. We actually need to format the cells instead.

Note: I am not quite sure if you need to go back 6 months or always until May, but I assumed it is 6 months starting from the input in the code below. If you need otherwise, then please let us know.

Sub doIt()
Dim i As Integer
Dim rng As Range
Dim userInput
Dim RepDate As Date

    ' Ask for the user input and split by using dash as parameter
    userInput = InputBox("Please enter date in MM-YY format", "Enter Date")
    userInput = Split(userInput, "-")
    
    ' Create the RepDate by using the input fragments
    RepDate = DateSerial(userInput(1), userInput(0), 1)
    
    ' Better to use a variable for the range
    ' instead of calling it with the Range function with address all the time
    Set rng = Range("L4")
    rng.Value = RepDate
    rng.NumberFormat = "mmm-yy"
    
    ' Oct to May, 6 months. Oct is the starting cell.
    ' So we need 5 cells  back to May
    For i = 1 To 5
        With rng.Offset(, -i)
            .Value = DateAdd("m", -i, RepDate)
            .NumberFormat = "mmm-yy"
        End With
    Next i
End Sub