Excel VBA: 更新行范围时更改单元格中的日期;代码延迟

huangapple go评论62阅读模式
英文:

Excel VBA: change date in cell when range in row is updated; laggy code

问题

我正在编写一段代码,以在更改列C到R中的任何单元格时更改列T中的日期。

该代码有效,但导致工作簿变得非常卡顿和慢。有没有办法使这段代码更加高效?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng As Range, rrow As Long
    Dim Rng As Range
    Set WorkRng = Intersect(Range("B1:R300"), Target)
    If Not WorkRng Is Nothing Then
        For Each Rng In WorkRng
            rrow = Rng.Row
            If Not Rng.Value = "" Then
                Cells(rrow, "T").Value = Now
                Cells(rrow, "T").NumberFormat = "dd/mm/yyyy h:mm"
            Else
                Cells(rrow, "T").ClearContents
            End If
        Next
    End If
End Sub
英文:

I am writing a code to change a date in column T when any cells in, column C to R are changed.

The code works but it's making the workbook super laggy and slow. Is there a way to make this code efficient?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng As Range, rrow As Long
    Dim Rng As Range
    Set WorkRng = Intersect(Range("B1:R300"), Target)
    If Not WorkRng Is Nothing Then
            For Each Rng In WorkRng
                rrow = Rng.Row
                If Not Rng.Value = "" Then
                    Cells(rrow, "T").Value = Now
                    Cells(rrow, "T").NumberFormat = "dd/mm/yyyy h:mm"
                Else
                    Cells(rrow, "T").ClearContents
                End If
            Next
    End If
End Sub

答案1

得分: 1

以下是您要翻译的内容:

如果要在列B:R中没有数据时清除时间戳,请尝试类似以下方式:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const FLAG_COL As String = "T"
    
    Dim WorkRng As Range, c As Range, rw As Range, cT As Range
    
    Set WorkRng = Intersect(Me.Range("B1:R300"), Target)
    If Not WorkRng Is Nothing Then
        For Each c In WorkRng.Cells
            Set rw = c.EntireRow
            Set cT = rw.Columns(FLAG_COL) '时间戳单元格
            '此行中的B:R是否有任何数据?
            If Application.CountA(rw.Range("B1:R1")) > 0 Then
                cT.Value = Now
                cT.NumberFormat = "dd/mm/yyyy h:mm"
            Else
                cT.ClearContents
            End If
        Next
    End If
End Sub
英文:

Try something like this if you want to clear the timestamp when a row has no data in columns B:R

Private Sub Worksheet_Change(ByVal Target As Range)
    Const FLAG_COL As String = "T"
    
    Dim WorkRng As Range, c As Range, rw As Range, cT As Range
    
    Set WorkRng = Intersect(Me.Range("B1:R300"), Target)
    If Not WorkRng Is Nothing Then
        For Each c In WorkRng.Cells
            Set rw = c.EntireRow
            Set cT = rw.Columns(FLAG_COL) 'timestamp cell
            'any data in B:R on this row?
            If Application.CountA(rw.Range("B1:R1")) > 0 Then
                cT.Value = Now
                cT.NumberFormat = "dd/mm/yyyy h:mm"
            Else
                cT.ClearContents
            End If
        Next
    End If
End Sub

答案2

得分: 0

以下是您要翻译的代码部分:

工作表中所选范围的每一行更改时都会更新时间戳,如果行中的所有单元格都没有值,则删除时间戳。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkRange As Range, changedRange As Range, wrkRow As Range, rrow As Long
    Dim Rng As Range

    Err.Clear
    On Error GoTo Lerr
    Application.EnableEvents = False
    Set checkRange = Range("B1:R300")
    Set changedRange = Intersect(checkRange, Target)
    If Not changedRange Is Nothing Then
        For Each Rng In changedRange
            rrow = Rng.Row
            If Rng.Value = vbNullString Then
                Set wrkRow = Intersect(Rng.EntireRow, checkRange)
                If WorksheetFunction.CountA(wrkRow) = 0 Then
                    Cells(rrow, "T").ClearContents
                Else
                    GoTo LsetTimeStamp
                End If
            Else
LsetTimeStamp:
                Cells(rrow, "T").Value = Now()
                Cells(rrow, "T").NumberFormat = "dd/mm/yyyy h:mm:ss"
            End If
        Next
    End If
Lerr:
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox ("错误> " & Err.Number & vbCrLf & Err.Description)
    End If
    On Error GoTo 0
End Sub

如果您需要更多帮助或有其他问题,请随时告诉我。

英文:

The timestamp is updated on each row change in the selected Range and is deleted if all cells in the row have no value.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim checkRange As Range, changedRange As Range, wrkRow As Range, rrow As Long
   Dim Rng As Range
   
   Err.Clear
   On Error GoTo Lerr
   Application.EnableEvents = False
   Set checkRange = Range(&quot;B1:R300&quot;)
   Set changedRange = Intersect(checkRange, Target)
   If Not changedRange Is Nothing Then
      For Each Rng In changedRange
         rrow = Rng.row
         If Rng.value = vbNullString Then
            Set wrkRow = Intersect(Rng.EntireRow, checkRange)
            If WorksheetFunction.CountA(wrkRow) = 0 Then
               Cells(rrow, &quot;T&quot;).ClearContents
            Else
               GoTo LsetTimeStamp
            End If
         Else
LsetTimeStamp:
            Cells(rrow, &quot;T&quot;).value = Now()
            Cells(rrow, &quot;T&quot;).NumberFormat = &quot;dd/mm/yyyy h:mm:ss&quot;
         End If
      Next
   End If
Lerr:
   Application.EnableEvents = True
   If Err.Number &lt;&gt; 0 Then
      MsgBox (&quot;Error&gt; &quot; &amp; Err.Number &amp; vbCrLf &amp; Err.Description)
   End If
   On Error GoTo 0
End Sub

huangapple
  • 本文由 发表于 2023年6月12日 22:20:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/76457579.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定