excel 如果单元格包含列表中的特定文本,则进行累加

ttisahbt  于 2023-05-19  发布在  其他
关注(0)|答案(2)|浏览(234)

我想在单元格中每次出现“DELAYED”一词时累积一个值(这样我就可以检查它发生了多少次,即使它是关闭的或计划的)。

我希望它每次改变时都能自动发生。

Private Sub Worksheet_Change(ByVal Target As Range)

If InStr(1, (Range("FX194:GK194").Value), "delayed") > 0 Then
    Range("GO194").Value = Range("GO194").Value + 1
End If

End Sub
vu8f3i0k

vu8f3i0k1#

请尝试下一个经过调整的事件代码:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LastR As Long
 LastR = Me.UsedRange.SpecialCells(xlCellTypeLastCell).row

 If Not Intersect(Target, Me.Range("FX4:GK" & LastR)) Then 'the range in which the event will be triggered
    Application.EnableEvents = False
     Me.Range("GO" & Target.row).Value = Me.Range("GO" & Target.row).Value + 1
    Application.EnableEvents = False
 End If
End Sub

您不需要更新尚未更改的单元格...

gr8qqesn

gr8qqesn2#

您需要检查target-因为这是更改的范围。
调用一个完成您想要的工作并相应命名的sub,可以提高代码的可读性。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c as Range

For each c in Target.cells  'in case multiple cells got changed
    If InStr(1, c.Value, "delayed") > 0 Then
        updateTotalDelayed c
    End If
Next

End Sub

Private Sub updateTotalDelayed(c as range)
Application.EnableEvents = False
With me.range("GO" & c.row) 'always use explicit referencing - in this case me represents the sheet where the event occured.
    .value = .value + 1
End With
Application.EnableEvents = True
End Sub

相关问题