none
Need help with completing a VBA RRS feed

  • Question

  • Hi,

         I am currently using this formula for showing data every minute:

    Private Sub Worksheet_change(ByVal Target As Range)
       If Target.Address = "$A$1" Then
          With ThisWorkbook.Names("listdde").RefersToRange.CurrentRegion
             With .Offset(.Rows.Count, 0).Resize(1, 1)
                 .Value = Now
                 .Offset(0, 1).Value = Target.Value
              End With
          End With
       End If
    End Sub

     How do I get this macro to return to the initial cell (B2) when it reaches 24 hours (b1441), so it loops?

     On top of that I would like that as a cell is filled I would like that the fifth cell in front of it is cleared, as this would otherwise affect my other formulas.

    For example when b2 gives a reading, B7 is then wiped. B3 gives a reading, B8 is wiped. Also when B1437 gives a reading then B2 would be wiped.

    Help would be very much appreciated

    Thanks

    Wednesday, March 14, 2018 2:22 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, March 15, 2018 6:18 AM
  • Hello SabrinaSS,

    Please check if below demonstration could roughly meet your need. If it does, please try to adjust below code for your need.

    In my test, I used B1 as "listdde" range and use 12 as 1440 for benefit for demonstration.

    Here is the code I used.

    Private Sub Worksheet_change(ByVal Target As Range)
    Application.EnableEvents = False
    maxValue = 12
       If Target.Address = "$A$1" Then
          With ThisWorkbook.Names("listdde").RefersToRange.CurrentRegion
            Set Rng = ThisWorkbook.Names("listdde").RefersToRange.CurrentRegion
            RowCount = .Rows.Count
    
            If RowCount = maxValue + 1 Then
               RowCount = 1
            End If
    
            If (RowCount + 5) <= maxValue Then
            Range(.Offset(RowCount, 0).Resize(1, 1), .Offset(RowCount + 5, 1).Resize(1, 1)).ClearContents
            ElseIf (RowCount < maxValue) Then
            Range(.Offset(RowCount, 0).Resize(1, 1), .Offset(maxValue, 1).Resize(1, 1)).ClearContents
            End If
    
    
            With .Offset(RowCount, 0).Resize(1, 1)
                 .Value = Now
                 .Offset(0, 1).Value = Target.Value
            End With
    
          End With
       End If
    Application.EnableEvents = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 16, 2018 2:13 AM