英文:
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("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 ("Error> " & Err.Number & vbCrLf & Err.Description)
End If
On Error GoTo 0
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论