Results 1 to 6 of 6

Thread: Date loop

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Date loop

    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!

    Code:
    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Date loop

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

    It can also be like this:

    Code:
    Sub temp2()
      With Range("B2:B4")
        .Formula = "=EOMONTH(TODAY(),-1)"
        .Value = .Value
      End With
    End Sub
    Last edited by DanteAmor; Sep 19th, 2019 at 06:32 PM.
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date loop

    Thank you Dante!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Date loop

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

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Date loop

    It can be done even more directly
    Code:
    Sub temp3()
      Range("B2:B4").Value = Date - Day(Date)
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,250
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Date loop

    Quote Originally Posted by Peter_SSs View Post
    It can be done even more directly
    Code:
    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...
    Code:
    Sub temp3()
      [B2:B4] = Date - Day(Date)
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •