Accumulate if a cell contains specific Text from a list-CodePudding

I'm a noob at VBA and I've been struggling to make this work so any help would be much appreciated.

I want to accumulate value in a cell everytime the word "DELAYED" appears (so i can check how many times it happened even if it was closed or scheduled afterwards).

(this is what it looks like)

I want it to happen automatically every time it changes. Here is where I'm stuck:


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

Thanks a lot in advance!

CodePudding user response：

Please, try the next adapted event code:

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

You do not need updating the cells which have not been changed...

CodePudding user response：

You need to check the target- as this is the changed range.

Calling a sub that does the job you want and is named accordingly, improves readability of your code.


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