Nested If Statements with Intersect

[crookesa]

I am watching a number of named ranges on one sheet and want to try make my code a simple as possible.

Each named range I am watching corresponds to a day of the month so it can be up to 31 named ranges.

I have the following working for one range

If Not Intersect(Target, Range("day_1")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(1)
End If

What I was trying to do was

If Not Intersect(Target, Range("day_1")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(1)
Else If Not Intersect(Target, Range("day_2")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(2)
.... and so on
End If

I get the error "Must be first statement on the line".
Is it ok to list the first code snippet 31 times to watch for each named range (even if it doesn't exists, e.g. 28 days in feb) or is that inefficient.

I will have the same code running on 2 other sheets as each sheet represents a different employee grade and has to be separated like that.
Also, should I be looking for other problems regarding error catching or exiting subs, etc.
First time using this intersect function on Worksheet Changes.

 

[QuietRiot]

Thats just a formatting error and it just needs to be on a new line like my code below. However that will get messy. You can probably just use a loop or maybe select case statement is better?

For i = 1 To 31
    If Not Intersect(Target, Range("day_" & i)) Is Nothing Then
        Call ThisWorkbook.update_day_duties(i)
    End If
Next

..

If Not Intersect(Target, Range("day_1")) Is Nothing Then
    Call ThisWorkbook.update_day_duties(1)
Else
    If Not Intersect(Target, Range("day_2")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(2)
    Else
        If Not Intersect(Target, Range("day_3")) Is Nothing Then
             Call ThisWorkbook.update_day_duties(3)
        Else
            'and so on
        End If
    End If
End If

 

[jasonb75]

Thats just a formatting error and it just needs to be on a new line like my code below.

That is partially correct, the formatting error was actually the extra space, it should be ElseIf not Else If

If Not Intersect(Target, Range("day_1")) Is Nothing Then
    Call ThisWorkbook.update_day_duties(1)
ElseIf Not Intersect(Target, Range("day_2")) Is Nothing Then
    Call ThisWorkbook.update_day_duties(2)
ElseIf Not Intersect(Target, Range("day_3")) Is Nothing Then
     Call ThisWorkbook.update_day_duties(3)
ElseIF
            'and so on
End If

Although, as you rightly say, there are better ways.

 

[Peter_SSs]

Edit: Ah, I see Jason beat me to it.
You can continue to use your existing code structure, you just need to remove the space between 'Else' and 'If'

  If Not Intersect(Target, Range("day_1")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(1)
  ElseIf Not Intersect(Target, Range("day_2")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(2)
  .... and so on

 

[crookesa]

I see both answers now (and noticed that myself after the post) and thanks for the feedback.

For i = 1 To 31
    If Not Intersect(Target, Range("day_" & i)) Is Nothing Then
        Call ThisWorkbook.update_day_duties(i)
    End If
Next

@QuietRiot i presume it's more efficient to only run my code when the cells within my range change rather than running the code across all range when one range value changes.
The update_day_duties() function takes in a value for the day_ parameter and runs against that named range.

Would I be best to use my if elseif end format or a select case statement as suggested?

 

[jasonb75]

The method that crookesa suggested is only going to run on the range where the change is made. The absense of Else means that nothing happens to the remaining ranges.

You could insert Exit For to eliminate the need for testing any remaining ranges after the valid intersection has been found.

 

[QuietRiot]

@QuietRiot i presume it's more efficient to only run my code when the cells within my range change rather than running the code across all range when one range value changes.
The update_day_duties() function takes in a value for the day_ parameter and runs against that named range.

Would I be best to use my if elseif end format or a select case statement as suggested?

A switch statement is usually more efficient and faster than a set of nested ifs. I'm not sure that is going to make much of a difference in this case though. Like jason mentioned you could include an exit for to make that loop more efficient.

For i = 1 To 31
    If Not Intersect(Target, Range("day_" & i)) Is Nothing Then
        Call ThisWorkbook.update_day_duties(i)
        Exit For
    End If
Next

 

[crookesa]

Thanks for all the help, I'll tip away on it and see what I can get working efficiently.
Happy Christmas

 

[Fluff]

Are you named ranges a single cell, or a range of cells?

 

[crookesa]

Are you named ranges a single cell, or a range of cells?

At the moment they’re a range of cells and should stay like that. It’s a 2D array with the rows representing each day and the columns the number of employees. The range on the spreadsheet are inverted so thought it would be easier to have a defined number of rows in the 2D array and let the columns be redimed depending on the numbers of employees.