Insert a column with the last date of last month in each cell

Question

I am new to VBA. I am writing a code i VBA and in my subsection I want to write a code that sets up a column (with a range) that shows the last date of the last month. So in my case I want it to have 30/04/2022 in every cell from A3:A40, but if I run the code in july it then has to have the 31/05/2022 and so on and so on.

Can anyone help a beginner out?

Answer 1

Please, test the next code. It should do what you need:

Sub testLastDayPrevMonthInRange()
    Dim sh As Worksheet, rng As Range, lastD As Date, refDate As Date
    
    Set sh = ActiveSheet
    Set rng = sh.Range("A3:A40")
    
    refDate = Date 'you may change it in any date to test the code...
    lastD = refDate - (Day(refDate))
    rng.value = lastD
End Sub

Please, next time you place a question you should show us a piece of code, like a prove that you made researches on your own. We here do not offer free code writing services... We only help people correcting their code problems and learn.