鑒于此電子表格
我正在嘗試跟蹤磁盤被簽出的次數。這是我轉換為檢查行而不是列的 VBA。我從 robinCTS 的一篇文章中復制了這段代碼并進行了更改。我看到的問題是，當單元格 B3 中的值發生更改時，單元格 B8 會添加 1（正確）。如果我更改單元格 D13 中的值，則單元格 D8 會再次更新（不正確，它應該是單元格 D18）。它在正確的列中，只是錯誤的行。
'============================================================================================
' Module : <The appropriate sheet module>
' Version : 1.0
' Part : 1 of 1
' References : N/A
' Source : https://stackoverflow.com/a/47405528/1961728
'============================================================================================
Option Explicit
Private Sub Worksheet_Change _
( _
ByVal Target As Range _
)
Const s_CheckRow As String = "3:3,13:13"
Const s_CountRow As String = "8:8,18,18"
If Intersect(Target, Range(s_CheckRow)) Is Nothing Then Exit Sub
Dim rngCell As Range
For Each rngCell In Intersect(Target, Range(s_CheckRow))
With Range(s_CountRow).Cells(rngCell.Column)
.Value2 = IIf(.Value2 <> vbNullString, .Value2 1, IIf(rngCell.Value2 <> vbNullString, 1, vbNullString))
End With
Next rngCell
End Sub
那么我做錯了什么？
uj5u.com熱心網友回復：
而不是Const s_CountRow As String = "8:8,18,18"（錯字那里？）我會使用每個更改單元格的固定偏移量：
Private Sub Worksheet_Change(ByVal Target As Range)
Const RNG_CHECK As String = "3:3,13:13"
Const ROW_OFFSET As Long = 5
Dim rng As Range, c As Range
Set rng = Application.Intersect(Target, Range(RNG_CHECK))
If rng Is Nothing Then Exit Sub
For Each c In rng.Cells
If Len(c.Value) > 0 Then
c.Offset(ROW_OFFSET).Value = c.Offset(ROW_OFFSET).Value 1
End If
Next c
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405378.html
標籤：