How can I make a loop to run this code 15 times using a list of the range variables I defined?

how to create a list in python using for loop
create new list in for loop python
create multiple lists using for loop
list comprehension python
python for loop index
python append to list while iterating
use loops to print out the elements in the list a
python for loop increment

I am running the same code for each of 15 static Ranges on the (current) Worksheet_Change event. Each range is evaluated by itself.

I am checking for duplicates, but not across ranges, only in each individual range. But all ranges must be checked (or up until action) before the procedure ends.

The cells are being filled by user selections from dynamic drop down lists for all cells.

The code I am posting works just as I need it to. How can I make a loop to run the same code 15 times using a list of the range variables I defined?

I want to simplify the code so that if I make a change to the code I don't have to change it in 15 places.

I have tried several versions of researched code to make a looping code functional, but the multiple If statements make it hard for me to find the right structure for a loop.

I finally gave up and copied the code 15 times in an If - ElseIf statement which works.

Private Sub Worksheet_Change(ByVal Target As Range)

'Define your variables.
Dim Sun1AM As Range, Sun1PM As Range, Wed1PM As Range
Dim Sun2AM As Range, Sun2PM As Range, Wed2PM As Range
Dim Sun3AM As Range, Sun3PM As Range, Wed3PM As Range
Dim Sun4AM As Range, Sun4PM As Range, Wed4PM As Range
Dim Sun5AM As Range, Sun5PM As Range, Wed5PM As Range

'Set the ranges where you want to prevent duplicate entries.
Set Sun1AM = Range("C4:C14")
Set Sun1PM = Range("C17:C21")
Set Wed1PM = Range("C24:C28")
Set Sun2AM = Range("E4:E14")
Set Sun2PM = Range("E17:E21")
Set Wed2PM = Range("E24:E28")
Set Sun3AM = Range("G4:G14")
Set Sun3PM = Range("G17:G21")
Set Wed3PM = Range("G24:G28")
Set Sun4AM = Range("I4:I14")
Set Sun4PM = Range("I17:I21")
Set Wed4PM = Range("I24:I28")
Set Sun5AM = Range("K4:K14")
Set Sun5PM = Range("K17:K21")
Set Wed5PM = Range("K24:K28")


'See if target is in any of the ranges defined above and check for 
'duplicates range by range.
If Not Intersect(Target, Sun1AM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun1AM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _ 
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun1PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun1PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Wed1PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Wed1PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun2AM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun2AM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun2PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun2PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Wed2PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Wed2PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun3AM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun3AM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun3PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun3PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Wed3PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Wed3PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun4AM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun4AM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun4PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun4PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Wed4PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Wed4PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun5AM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun5AM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Sun5PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Sun5PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

ElseIf Not Intersect(Target, Wed5PM) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
        If WorksheetFunction.CountIf(Wed5PM, Target.Value) > 1 Then _
           MsgBox Target.Value & " is already used.", vbInformation, _
           "Duplicate Entry!"

Else
    Exit Sub

End If

End Sub

This works but very hard to manage. Someone please enlighten me to a nice simple loop. I will be copying this sheet, making a new copy each month so the code must remain "current sheet" and work on any sheet the user is working on.

Many Thanks!!!

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, a As Range

    If Target.CountLarge > 1 Then Exit Sub 'only need this test once

    Set rng = Me.Range("C4:C14,C17:C21,C24:C28") 'start here
    Do While rng.Column <= 11
        'loop over the areas in the range
        For Each a In rng.Areas
            'edit 2 missed this entire check...
            If not Application.Intersect(a, Target) Is Nothing Then
                If Application.CountIf(a, Target.Value) > 1 Then
                    MsgBox Target.Value & " is already used in range " & a.Address, _
                           vbInformation, "Duplicate Entry!"
                    Exit Do
                End If
            End If

        Next a
        Set rng = rng.Offset(0, 2) 'move two columns to the right
    Loop

End Sub

How can I make a loop to run this code 15 times using a list of, The cells are being filled by user selections from dynamic drop down lists for all cells. How can I make a loop to run the same code 15 times using a list of the range variables I defined? I want to simplify 'Define your variables. 'See if target is in any of the ranges defined above and check for 'duplicates range by range. For loop with range. In the previous lessons we dealt with sequential programs and conditions. Often the program needs to repeat some block several times. That's where the loops come in handy. There are for and while loop operators in Python, in this lesson we cover for. for loop iterates over any sequence.

If the pattern of ranges is exactly same then may try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim SrcRng As Range, URng As Range
Dim Cl As Long, EndRw As Long, StartRw As Long, EndRwID As Long

    For Cl = 3 To 11 Step 2
        For EndRwID = 2 To 4
        EndRw = EndRwID * 7
        StartRw = IIf(EndRwID = 2, EndRw - 10, EndRw - 4)
        Set SrcRng = Range(Cells(StartRw, Cl), Cells(EndRw, Cl))

        'See if target is in any of the ranges defined above and check for
        'duplicates range by range.
            If Not Intersect(Target, SrcRng) Is Nothing Then
                If WorksheetFunction.CountIf(SrcRng, Target.Value) > 1 Then _
                MsgBox Target.Value & " is already used.", vbInformation, _
                "Duplicate Entry!"
            Exit For
            End If

    Next EndRwID
    Next Cl
End Sub

Or else If ranges require d to be tested does not always follow a pattern then may try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

Dim SrcRng(1 To 15) As Range
Dim i As Long

Set SrcRng(1) = Range("C4:C14")
Set SrcRng(2) = Range("C17:C21")
Set SrcRng(3) = Range("C24:C28")
Set SrcRng(4) = Range("E4:E14")
Set SrcRng(5) = Range("E17:E21")
Set SrcRng(6) = Range("E24:E28")
Set SrcRng(7) = Range("G4:G14")
Set SrcRng(8) = Range("G17:G21")
Set SrcRng(9) = Range("G24:G28")
Set SrcRng(10) = Range("I4:I14")
Set SrcRng(11) = Range("I17:I21")
Set SrcRng(12) = Range("I24:I28")
Set SrcRng(13) = Range("K4:K14")
Set SrcRng(14) = Range("K17:K21")
Set SrcRng(15) = Range("K24:K28")


        For i = 1 To 15
        'See if target is in any of the ranges defined above and check for
        'duplicates range by range.
            If Not Intersect(Target, SrcRng(i)) Is Nothing Then
            If WorksheetFunction.CountIf(SrcRng(i), Target.Value) > 1 Then _
            MsgBox Target.Value & " is already used.", vbInformation, _
            "Duplicate Entry!"
            Exit For
            End If
      Next i
End Sub

Loops and lists, With a variable that refers to the list, we can work with the whole group at C = [-​10, -5, 0, 5, 10, 15, 20, 25, 30] # create list >>> C.append(35) # add new In fact, every object in Python and everything you can do with it is defined by We then start with an empty list and use a while loop to append one element at a time:. The short answer is that you don’t — the for loop doesn’t bring anything to the table that the while loop can’t already do. However, the sections of the for loop exist for convenience — and to clearly establish the three parts that every loop should have: the setup, exit criteria, and increment.

Answer courtesy of Tim Williams (see post above) https://stackoverflow.com/users/478884/tim-williams

Note: this code checks for the user entering a duplicate value in the ranges C4:C14,C17:C21,C24:C28,E4:E14,E17:E21,E24:E28,G4:G14,G17:G21,G24:G28, I4:I14,I17:I21,I24:I28,K4:K14,K17:K21,K24:C28 only.

These are static ranges of assignments on a dynamic monthly assignment calendar. This code does not delete or prevent a duplicate entry. It only advises the user with a vbInformation message box that a person has been assigned more than one task on a given day. It notifies that "someone" has already been used, and the user may choose to leave or edit the duplicate. This sheet (a master copy) is copied as a new blank sheet for each month, assignments are filled in and printed copies are distributed. The sheet itself changes dynamically to reflect the proper calendar dates once a month and year are chosen. This code is designed to work on the "active" worksheet since only one month (one sheet) is being assigned at a time, and past months remain as reference documents.

Private Sub Worksheet_Change(ByVal Target As Range)    'By Tim Williams

Dim rng As Range, a As Range

If Target.CountLarge > 1 Then Exit Sub 'only need this test once
If IsEmpty(Target) Then Exit Sub       'added check for empty target on delete action

Set rng = Range("C4:C14,C17:C21,C24:C28") 'start here
Do While rng.Column <= 11
    'loop over the areas in the range
    For Each a In rng.Areas
        If Not Intersect(Target, a) Is Nothing _
           And WorksheetFunction.CountIf(a, Target.Value) > 1 Then
            MsgBox Target.Value & " is already used", _
            vbInformation, "Duplicate Entry!"

            Exit Do
        End If    

    Next a
    Set rng = rng.Offset(0, 2) 'move two columns to the right
Loop

End Sub

Many thanks to Tim for showing me how to simplify my bulky code to an incredibly neat and simple routine.

How to program range loops in C#? · Kodify, We code a C# range loop of sequential integers with the Range() method. Interestingly, when we make a foreach loop with the Range() method like this, But now we start the range at -10, and then increase that value 15 times. In the loop we access each list value with that i variable ( values[i] ), and  For Loop . A "For" Loop is used to repeat a specific block of code a known number of times. For example, if we want to check the grade of every student in the class, we loop from 1 to that number. When the number of times is not known before hand, we use a "While" loop.

1.13. Loops and Sequences, There are two Python statement types to do that: the simpler for loops, which we The value of already defined variables can be updated. With more parameters, the range function can be used to generate a much Predict the change, and run the code again to test. tripleAll([2, 4, 1, 5]) 6 12 3 15 >>> tripleAll([-6]) -18 '''  This type of loop is great for cycling through a pre-determined number of times. The loop needs to begin with the word " For " and then a variable name followed by an equals sign (=) and finally the range from which you want your variable to equal during the loop (ie 15 to 25 or 1 to 5).

Loop control statements, In Python, this kind of loop is defined with the for statement, which executes the the loop body is executed for the first time – if the condition is false at the start, the Whenever you write a while loop, make sure that the variable you use in your 2, 3), (4, 5, 6)): print(i) for i in zip(range(5), range(5, 10), range(10, 15)): print(i). The "While" Loop . A "While" Loop is used to repeat a specific block of code an unknown number of times, until a condition is met. For example, if we want to ask a user for a number between 1 and 10, we don't know how many times the user may enter a larger number, so we keep asking "while the number is not between 1 and 10".

7. Iteration, 15 7. because the first time airtime_remaining is printed, its value is 15, and the second In Python, an assignment statement can make two variables equal, but because Running through all the items in a list is called traversing the list, or traversal. Notice that if the loop condition is False the first time we get loop, the​  However, it does help you see where a loop ends, and is particularly useful when you have one loop within another (see the example at the bottom of this page). A Simple Example. Use this type of loop when you know exactly how many times you want to go through a loop.

Comments
  • This code fires the Message Box with any entry anywhere in any of the Ranges. Even if the entry is not a duplicate. I only want to fire if there is a duplicate in the range where the target is.
  • My bad, I had countif > 0 and not >1
  • I can't believe I missed that. I reviewed it like 10 times. I added back in the line to check if the duplicate was in the same range as the target. You can tell me if I don't need it. ' If Not Intersect(Target, a) Is Nothing And WorksheetFunction.CountIf(a, Target.Value) > 1 Then MsgBox Target.Value & " is already used", _ vbInformation, "Duplicate Entry!"' It totally works now.
  • You're right I missed the Target/a Intersect check altogether - that's what happens when skipping the testing...
  • I did need the above code to check if the target was in the current range. Otherwise the duplicate message box fired when the target was outside any of the ranges but the name was already used in one of the ranges. You did an awesome job. Thanks so much. I will post my final code as the answer.
  • Since I was already working with Tim Williams solution I was far into completing this task. Please see my final posed code above for what worked and what modifications I made to Tim's submitted code.