时间戳

首页 > 解决方案 > 即使数据没有变化也记录值

excel - 即使数据没有变化也记录值

问题描述

我的工作表每 3 分钟刷新一次并从网站获取数据，此代码每 3 分钟记录一次数据，即使“o1:v1”范围内的值没有变化。但我希望它仅在此范围内的至少一个单元格发生变化时记录。请帮忙！

Private Sub Worksheet_Calculate()

    With Worksheets("record")
       .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, "B").Resize(, 8).Value = Worksheets("record").Range("O1:V1").Value
       .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, "A").Value = Now()
    End With

End Sub

标签： excelvba

解决方案

记录数据更改 ( Worksheet_Calculate)

Option Explicit

Private Sub Worksheet_Calculate()

    ' Ranges: Source and Previous Destination
    Dim srg As Range, drg As Range
    Dim cCount As Long
    With Worksheets("record")
        Set srg = .Range("O1:V1")
        cCount = srg.Columns.Count
        Set drg = .Cells(.Cells(.Rows.Count, "A") _
            .End(xlUp).Row, "B").Resize(, cCount)
    End With
    
    ' Arrays
    Dim sData As Variant: sData = srg.Value
    Dim dData As Variant: dData = drg.Value
    
    ' Compare
    Dim c As Long
    For c = 1 To cCount
        If sData(1, c) <> dData(1, c) Then
            Exit For
        End If
    Next c
    If c > cCount Then Exit Sub ' no change
    
    ' New Destination
    Set drg = drg.Offset(1): drg.Value = sData
    drg.Resize(, 1).Offset(, -1).Value = Now

End Sub

推荐阅读