VBA CODE not working no errors

[Dark0Prince]

This code should keep adding up when i type in certain columns if the words Grand Totals or Totals aren't there. But doesn't seem to work anymore.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myrange As Range
    Dim c As Range

    Dim myArray As Variant
    Dim a As Long
    Dim skip As Boolean
    
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub  '<= moved up
    
    myArray = Array("Grand Totals", "TOTALS")    'adjust data as needed
    
    Application.EnableEvents = False
    
    Set myrange = Intersect(Target, Range("M6:M2000")) '<- adjust range as needed
    If Not myrange Is Nothing Then
        For Each c In myrange
            For a = 0 To UBound(myArray)
                If Cells(c.Row, 2) = myArray(a) Then skip = True    'test cell in column B
            Next a
            If skip = False Then Cells(c.Row, 15).Value = Cells(c.Row, 15).Value + c
        Next c
    End If
    
    Set myrange = Intersect(Target, Range("N6:N2000")) '<- adjust range as needed
    If Not myrange Is Nothing Then
        For Each c In myrange
            For a = 0 To UBound(myArray)
                If Cells(c.Row, 2) = myArray(a) Then skip = True    'test cell in column B
            Next a
            If skip = False Then Cells(c.Row, 16).Value = Cells(c.Row, 16).Value + c
        Next c
    End If
    
    If Not Intersect(Target, Range("M6:M2000")) Is Nothing Then '<- adjust range as needed for auto date
        Columns("O").AutoFit
    End If
    
    Application.EnableEvents = True     '<= moved down


        
    Set myrange = Intersect(Target, Range("T6:T2000")) '<- adjust range as needed
    If Not myrange Is Nothing Then
        For Each c In myrange
            For a = 0 To UBound(myArray)
                If Cells(c.Row, 2) = myArray(a) Then skip = True    'test cell in column B
            Next a
            If skip = False Then Cells(c.Row, 21).Value = Cells(c.Row, 21).Value + c
        Next c
    End If
    
    Set myrange = Intersect(Target, Range("V6:V2000")) '<- adjust range as needed
    If Not myrange Is Nothing Then
        For Each c In myrange
            For a = 0 To UBound(myArray)
                If Cells(c.Row, 2) = myArray(a) Then skip = True    'test cell in column B
            Next a
            If skip = False Then Cells(c.Row, 23).Value = Cells(c.Row, 23).Value + c
        Next c
    End If
    
    If Not Intersect(Target, Range("M6:M2000")) Is Nothing Then '<- adjust range as needed for auto date
        Columns("Q").AutoFit
    End If
    
    Application.EnableEvents = True     '<= moved down
    
        Set myrange = Intersect(Target, Range("X6:X2000")) '<- adjust range as needed
    If Not myrange Is Nothing Then
        For Each c In myrange
            For a = 0 To UBound(myArray)
                If Cells(c.Row, 2) = myArray(a) Then skip = True    'test cell in column B
            Next a
            If skip = False Then Cells(c.Row, 25).Value = Cells(c.Row, 25).Value + c
        Next c
    End If
    
    If Not Intersect(Target, Range("M6:M2000")) Is Nothing Then '<- adjust range as needed for auto date
        Columns("Q").AutoFit
    End If

End Sub

 

[NdNoviceHlp]

I don't see "Grand Totals or Totals " anywhere in the code??? Dave

 

[Dark0Prince]

myArray = Array("Grand Totals", "TOTALS") 'adjust data as needed

 

[Joe4]

Perhaps your code got interrupted and your events are still disabled.

Try running this short procedure to turn them back on and try again:

Sub TurnOnEvents()
    Application.EnableEvents = True
End Sub

If that does not fix it, what things have changed since it last worked?
That is usually your best place to start.

 

[Dark0Prince]

Yep that fixed it, does that mean I only need that

 Application.EnableEvents = True

line in there once at the end of the code?

 

[Joe4]

It is important to understand what is happening here.
Event procedure code is code that is automatically triggered upon some event happening. In the case of a "Worksheet_Change" event procedure, it is the updating of a cell (or cells) that triggers it to run.
So, a cell is changed, and the code runs automatically. But what happens if your code, itself, is changing cells?
Then the code that is running and changing cells may be triggering the code to run again.
If you are not careful, you can get caught in an infinite loop and crash Excel!

Typically, we do not the changes made by our code to call itself to run again. So we must disable the automated event code from running. That is what this line does:

Application.EnableEvents = False

However, after the code completes its changes, we want to turn it back on, so our manual changes will keep triggering the code. That is what this line does:

Application.EnableEvents = True

Where problems can arise is if for something reason, your code stops part way through (maybe an error, maybe you are stepping through your code), and the disable event line of code runs, but the re-enable one doesn't. Then, you end up in the situation you experienced. So, you just want to re-enable the events so the code will run again.

You can do that by running code to do it (like I provided). Completely closing Excel and re-starting it will also reset that.

If you have situations where your code may error out before getting to the end and running that re-enable event line of code, you may want to add error handling to your code that turns it back on before exiting.