Named Ranges upsetting code

[ddub25]

This is a snippet of code where I have recently adapted some of the cells to Named Ranges and I think it might be upsetting the code as the code isn't working.

For i = 0 To 9
If Range("SetP1").Value + Range("SetP2").Value = 0 And Range("GaP1").Value + Range("GaP2").Value = i Then
     Range("CT" & (85 + i)).Value = Server
End If
Next i

The code used to be as below and worked fine.

For i = 0 To 9
If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = i Then
     Range("CT" & (85 + i)).Value = Server
End If
Next i

 

[Joe4]

See here for adding breakpoints: Setting Breakpoints and the VBA Stop Statement

There are various different ways to only look at relevant cells, and it often depends on what the range/conditions are. Target is the cell(s) that are updating (if using copy/paste, it could be multiple cells at once).

For example:

To look at column E only

If Target.Column <> 5 Then
    Exit Sub
End If

To look at a specific cell

If Target.Address <> "$C$10" Then
    Exit Sub
End If

To see if a cell is found within a specified range (B5:F163)

If Intersect(Target,Range("B5:F163")) Is Nothing Then
    Exit Sub
End If

 

[ddub25]

It seems there wasn't anything wrong with the code adapted to use Named Ranges. I tried rebooting Excel and that solved the problem. The Private Worksheet_Change Sub must have become corrupted even though any normal Sub Routines I have ran fine.

So, Named Ranges that are in fact ranges and not just a reference to a cell are able to be calculated as well.

Joe, thanks for for the additional info on 'Setting Breakpoints and the VBA Stop Statement', and only looking at relevant cells.

 

[Joe4]

I tried rebooting Excel and that solved the problem.

OK, I think I may know what happened here. It is good to know/understand for future reference.

Note at the beginning of your code you have this line:

Application.EnableEvents = False

and at the end of your code you have this line:

Application.EnableEvents = True

What this does is temporarily disable events while the code runs, so it doesn't call itself when the code starts changing cell values (leading you into an endless loop!). The line at the end turns it back on.

However, if at some point along the way, you started your code, but then stopped or exited before you got to the last line, then you never turned your events back on, meaning Event Procedure VBA will not run!

You either need to turn it back on by running a simple one line macro like this:

Sub TurnEventsOn()
    Application.EnableEvents = True
End sub

or if you close Excel and then re-open it, it will reset it back to the default settings (events enabled).

 

[ddub25]

Thanks Joe, very useful to know. Thanks for you help.

 

[Joe4]

You are welcome!