Named Ranges upsetting code

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
586
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.

Code:
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.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

JeanKim

Board Regular
Joined
May 31, 2016
Messages
183
upsetting the code as the code isn't working. ?????
You need to elaborate more.
 

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
586
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 31, 2016
Messages
183
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

Well-known Member
Joined
Jan 11, 2007
Messages
586

ADVERTISEMENT

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.

Code:
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


Code:
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2007
Messages
586
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?

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,097
Members
414,123
Latest member
fxledi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top