Date loop

[jwburritt]

Hello: I need a sub to input last month's date into a column of data and then hard code the date. The only solution I came up with is below, but I'm wondering why it's not creating a circular reference. Thank you! I'm sure it has something to do with equivalency, but I'm still new!

Sub temp()   
    Dim i As Long
    For i = 2 To 4
        Cells(i, 2).Formula = "=EOMONTH(TODAY(),-1)"
        Cells(i, 2).Value = Cells(i, 2).Value
    Next i


End Sub

 

[DanteAmor]

There is no circular reference because the formula does not use the same cell in the calculation.

It can also be like this:

Sub temp2()
  With Range("B2:B4")
    .Formula = "=EOMONTH(TODAY(),-1)"
    .Value = .Value
  End With
End Sub

 

[jwburritt]

Thank you Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

It can be done even more directly

Sub temp3()
  Range("B2:B4").Value = Date - Day(Date)
End Sub

 

[Rick Rothstein]

It can be done even more directly

Sub temp3()
  Range("B2:B4").Value = Date - Day(Date)
End Sub

If the range is really fixed and non-changing, this can be shortened to this...

Sub temp3()
  [B2:B4] = Date - Day(Date)
End Sub