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]

Is this an XLSX file?
If so, the issue is that the columns go out to XFD and the rows go down to 1048576.
So, if you have named ranges like "GaP1" and "GaP2", they can get confused with valid cell address of "GAP1" and "GAP2".

My advice is if using named ranges, do not pick any names that match the addresses of valid cells on your sheet.
So, make sure that the alpha prefix is at least four characters long, and you should be OK.

 

[JeanKim]

upsetting the code as the code isn't working. ?????
You need to elaborate more.

 

[ddub25]

Joe, those are not the true names of the Named Ranges - I have abbreviated them and so the true names won't cause any conflicts, sorry to cause confusion.

Jean, the first code example, where I have changed cell references to named ranges does not work. The second code example (my original code) does work. Why is that?

 

[Joe4]

Joe, those are not the true names of the Named Ranges - I have abbreviated them and so the true names won't cause any conflicts, sorry to cause confusion.

It is generally a bad idea to oversimplify the problem, as it can lead to incorrect assumptions, and doesn't give us a true picture of your problem.

So, what exactly is happening? Are you getting errors, or just unexpected results? Be sure to include any error messages.

Are all those named ranges single cells, or do any have multiple cells?
It will not work if any have multiple cells.

 

[JeanKim]

Jean, the first code example, where I have changed cell references to named ranges does not work. The second code example (my original code) does work. Why is that?

Your example should work if you did everything right.
I want to find out what you did wrong so please tell me what is the error code.
Then you may check each value Range("SetP1").Value; Range("SetP2").Value; Range("GaP1").Value; Range("GaP2").Value to see what value they hold.

 

[ddub25]

Here's the full code.
I am not getting any errors, it just doesn't do what it's supposed to do on a 'Worksheet_Change' as it did before when I used simple cell references as opposed to Named Ranges.
'Exe_Range1' is an actual range rather than just a reference to a cell (all the other named ranges are just references to cells).

I have tested this code in a normal 'Sub' and it works fine even with 'Exe_Range1' being a reference to a range. The 'Sub' test code is at the bottom.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    Dim Server As String
    If Range("ServerP1").Value = "FALSE" Then
        Server = "P2"
    Else
        Server = "P1"
    End If
    
    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CT" & (85 + i)).Value = Server
    End If
    Next i

    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CU" & (85 + i)).Value = Range("PtsP1").Value
    End If
    Next i
    
    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CV" & (85 + i)).Value = Range("PtsP2").Value
    End If
    Next i
    
    If Application.CountIf(Range("ServiceBreaks"), "P1 Break") And Application.CountIf(Range("ServiceBreaks"), "P2 Break") = 0 Then
        Range("TradeEntry1").Value = "Place..."
    End If
    If Range("TradeEntry1").Value = "Place..." Then
        Range("Exe_BL1").Value = "LAY"
        Range("Exe_Odds1").Value = Range("Curr_LayOdds1C").Value
        Range("Exe_Stake1").Value = Range("Stake").Value
    End If
    If Range("Exe_Status1").Value = "PLACED" Then
    Range("TradeEntry1").Value = "PLACED"
    Range("Exe_Range1").ClearContents
    End If
    
Application.EnableEvents = True
End Sub

Sub Test2()

    Dim Server As String
    If Range("ServerP1").Value = "FALSE" Then
        Server = "P2"
    Else
        Server = "P1"
    End If
    
    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CT" & (85 + i)).Value = Server
    End If
    Next i

    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CU" & (85 + i)).Value = Range("PtsP1").Value
    End If
    Next i
    
    For i = 0 To 9
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
            Range("CV" & (85 + i)).Value = Range("PtsP2").Value
    End If
    Next i
    
    If Application.CountIf(Range("ServiceBreaks"), "P1 Break") And Application.CountIf(Range("ServiceBreaks"), "P2 Break") = 0 Then
        Range("TradeEntry1").Value = "Place..."
    End If
    If Range("TradeEntry1").Value = "Place..." Then
        Range("Exe_BL1").Value = "LAY"
        Range("Exe_Odds1").Value = Range("Curr_LayOdds1C").Value
        Range("Exe_Stake1").Value = Range("Stake").Value
    End If
    If Range("Exe_Status1").Value = "PLACED" Then
    Range("TradeEntry1").Value = "PLACED"
    Range("Exe_Range1").ClearContents
    End If
    
End Sub

 

[Norie]

Can you post the Worksheet_Change event without the named ranges?

Also, what scope do the named ranges have?

Is it workbook scope or are they scoped to a specific worksheet?

If it's the latter then you will have to add the appropriate worksheet reference(s) to the named ranges.

 

[Joe4]

So, what exactly is this code supposed to do?

One thing that looks rather odd to me is how you are using Worksheet_Change. Without checking the "Target" cell (that is, the cell being updated), it is running ALL the code in there any time any change is made on the sheet. That is usually unnecessary and inefficient (especially with the amount of code and loops you have running there). Most of the time, you only need the code to run if certain, specific cells are being updated (or to certain values). So most Worksheet_Change procedures will have a check at the top to see if those conditions are being met, and if not, exit the Sub (just make sure to re-enable Events before exiting).

What I would recommend to you is to put a break point in your code near the top, and then make a change which should run your code. This will dump you into your code, and you can use the F8 to step through your code line-by-line, and you can see exactly what is happening and try to determine where/why the issue is happening. If you hover over any variable in your VBA code while you are stepped into it, it will show you its current value.

 

[ddub25]

Here's the original code without the named ranges. The named ranges are all on the same sheet (everything related to the code is on the same sheet)

Joe, that was going to be my next question - can I get the Worksheet_Change to only look at relevant cells rather than any change on the sheet?

How do I create a break point in this code that will allow me to jump into it?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    Dim Server As String
    If Range("CS73").Value = "FALSE" Then
        Server = "P2"
    Else
        Server = "P1"
    End If
    
    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

    For i = 0 To 9
    If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = i Then
            Range("CU" & (85 + i)).Value = Range("CV73").Value
    End If
    Next i
    
    For i = 0 To 9
    If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = i Then
            Range("CV" & (85 + i)).Value = Range("CV74").Value
    End If
    Next i
    
    If Application.CountIf(Range("CX85:CX94"), "P1 Break") And Application.CountIf(Range("CX85:CX94"), "P2 Break") = 0 Then
        Range("CN73").Value = "Place..."
    End If
    If Range("CN73").Value = "Place..." Then
        Range("L9").Value = "LAY"
        Range("M9").Value = Range("J9").Value
        Range("N9").Value = Range("CJ90").Value
    End If
    If Range("O9").Value = "PLACED" Then
    Range("CN73").Value = "PLACED"
    Range("L9:O9").ClearContents
    End If
    
Application.EnableEvents = True
End Sub