VBA For loop suddenly broken--what happened?

[tjdickinson]

This sub used to work perfectly. Then I added the variable misCell in the sub declaration and in the first line of code, and for some reason, the ElseIf statement in the first For loop is now broken. Nothing in that code changed (as far as I know). But suddenly it is filling all the "TOEZ. pauze" cells with "Toez. 4de pauze" (the last Else clause)--but, get this, EXCEPT row 10, which it fills correctly. Here's the code:

Sub renameBlocks(ttType As String, misCell As Range)

If ttType = "LK" Then
    Range(misCell.Address).Value = "MIS"
    Range("B9").Value = "Lunch"
    For Each Cell In Range("B3:F3,B6:F6,B10:F10,B13:F13")
        If Cell.Value = "pauze" Then
            Cell.Value = ""
        ElseIf Cell.Value = "TOEZ. pauze" Then
            If Cell.Row = 3 Then Cell.Value = "Toez. 1ste pauze" Else
            If Cell.Row = 6 Then Cell.Value = "Toez. 2de pauze" Else
            If Cell.Row = 10 Then Cell.Value = "Toez. 3de pauze" Else Cell.Value = "Toez. 4de pauze"
        End If
    Next
ElseIf ttType = "LL" Then
    Range(misCell.Address).Value = "MIS"
    Range("B9").Value = "Lunch"
    Range("B3").Value = ""
    For Each Cell In Range("C3,B6,B10,B13")
        Cell.Value = "PAUZE"
    Next
    Application.DisplayAlerts = False
    Range("C3:F3,B6:F6,B10:E10,B13:E13").MergeCells = True
    Application.DisplayAlerts = True
ElseIf ttType = "TZ" Then
    Range("A1").Value = "Toezicht 1ste pauze (8:00-8:20)"
    Range("A6").Value = "Toezicht 2de pauze (10:00-10:20)"
    Range("A11").Value = "Toezicht 3de pauze (12:30-13:00)"
    Range("A16").Value = "Toezicht 4de pauze (14:40-15:00)"
End If

End Sub

All the other code in this sub and in the full module works correctly.

When I put a break at the start of the For loop and step into the code:

• it runs in the correct For loop (so ttType = LK is TRUE)
• it correctly identifies when "TOEZ. pauze" is in the cell
• it correctly identifies the cell row (visible in the Locals window)
• it highlights (for example) Cell.Value = 3 Then in the code (which tells me that it's applying that line of code)
• but then it replaces "TOEZ. pauze" with "Toez. 4de pauze" (the final Else clause) instead of "Toez. 1ste pauze"

All the lines of code are the same except the row number, and the locals window has the correct row number. And the contents of the cells are correct: I even copied and pasted a "TOEZ. pauze" cell from row 10 into row 6 (so both cells are guaranteed to be identical), and it still replaced it with "4de pauze" instead of "2de pauze".

When I take the misCell variable out of the sub declaration and remove that code, it does the same thing, so it doesn't seem to be related to the misCell variable in any way.

Again, literally two days ago, this worked fine. And everything I'm getting from debugging tells me it should still work fine. I have no idea what's gone wrong.

 

[Fluff]

You currently have 3 different one line If statements, so if Cell.row =3 it will put "Toez. 1ste pauze" into the cell, but then when it gets to the last If, as Cell.Row is not 10 it goes to the else.
Try it like this instead

        ElseIf cell.Value = "TOEZ. pauze" Then
            Select Case cell.Row
               Case 3: cell.Value = "Toez. 1ste pauze"
               Case 6: cell.Value = "Toez. 2de pauze"
               Case 10: cell.Value = "Toez. 3de pauze"
               Case Else: cell.Value = "Toez. 4de pauze"
            End Select
        End If

 

[Joe4]

Also note that since "misCell" is already declared to be a range, this is totally unnecessary:

Range(misCell.Address).Value = "MIS"

Just use:

misCell.Value = "MIS"

Since it is already a range, why takes it address, and then turn that address back to the original range?
Just use the original range variable.

 

[tjdickinson]

You currently have 3 different one line If statements, so if Cell.row =3 it will put "Toez. 1ste pauze" into the cell, but then when it gets to the last If, as Cell.Row is not 10 it goes to the else.
Try it like this instead

        ElseIf cell.Value = "TOEZ. pauze" Then
            Select Case cell.Row
               Case 3: cell.Value = "Toez. 1ste pauze"
               Case 6: cell.Value = "Toez. 2de pauze"
               Case 10: cell.Value = "Toez. 3de pauze"
               Case Else: cell.Value = "Toez. 4de pauze"
            End Select
        End If

Once again, Fluff to the rescue--thank you, Fluff! It works perfectly. Very strange that it used to work fine...maybe I changed something without realising it. Your explanation makes perfect sense, and you've introduced me to a new function (Select Case), so I've learned something today. Three cheers for Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

@Joe4 the misCell is a range on another sheet, hence that line of code. I helped the OP with that on another thread.

 

[tjdickinson]

Also note that since "misCell" is already declared to be a range, this is totally unnecessary:

Range(misCell.Address).Value = "MIS"

Just use:

misCell.Value = "MIS"

Since it is already a range, why takes it address, and then turn that address back to the original range?
Just use the original range variable.

Thanks for the feedback, Joe4!

So, I had actually tried misCell.Value originally, and it didn't work--it didn't fill the cell with anything. I posted about it here, and Fluff gave me the Range(misCell.Address) line.

 

[Joe4]

@Joe4 the misCell is a range on another sheet, hence that line of code. I helped the OP with that on another thread.

Ah, OK. That makes sense now.
I did not have the benefit of that prior knowledge.

 

[tjdickinson]

Ah, OK. That makes sense now.
I did not have the benefit of that prior knowledge.

No harm, no foul! I'm a complete novice at VBA, and I'm building a rather large macro, so I'm always glad for feedback and advice, especially if it can optimise my code. You did the right thing, Joe4--thanks for it!