[VBA] Ending a loop after something has changed.

[Jeester]

Hi all,

I have a loop that searches a column to see if something has been posted there already, if it hasn't then it posts the new input and then adds checkboxes below it.
My issue is that I cannot stop it going onto the next column and doing the same. I would like it to stop the task after it has found a suitable column to post in.
I would do just a function to search for last empty column, however I have stuff above this point which means this would not be, as far as I know, possible.

My code:

'Find column to apply heading
For y = 4 To 29
If IsEmpty(ws2.Cells(5, y)) Then
ws2.Cells(5, y).Value = Me.IntName.Value



'Find rows to add checkboxes below column with heading
For x = 7 To 150
If Not IsEmpty(ws2.Cells(x, 3)) And ws2.Cells(x, 3).Value <> "EHCP" And ws2.Cells(x, 3).Value <> "SEN SUPPORT" And ws2.Cells(x, 3).Value <> "MONITOR" Then
ws2.Select
Cells(x, y).Select

'Add Checkboxes
MyLeft = ActiveCell.Left
MyTop = ActiveCell.Top
MyHeight = ActiveCell.Height
MyWidth = ActiveCell.Width


ws2.CheckBoxes.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height).Select
    With Selection
        .Value = xlOff
        .LinkedCell = Cells(x, y).Address
        .Display3DShading = True
        .Caption = ""
    End With


End If
Next x

End If
Next y

I think I need to use an "Exit For" somewhere but I do not know where, I have tried different places to no avail.

Cheers
Jeester

 

[trunten]

I haven't got excel installed at home, but try this.

For y = 4 To 29
    If IsEmpty(ws2.Cells(5, y)) Then
        ws2.Cells(5, y).Value = Me.IntName.Value
        For x = 7 To 150
            If Not IsEmpty(ws2.Cells(x, 3)) And ws2.Cells(x, 3).Value <> "EHCP" _
                    And ws2.Cells(x, 3).Value <> "SEN SUPPORT" _
                    And ws2.Cells(x, 3).Value <> "MONITOR" Then
                With s2.cells(x, y)
                    With ws2.CheckBoxes.Add(.Left, .Top, .Width, .Height)
                        .Value = xlOff
                        .LinkedCell = Cells(x, y).Address
                        .Display3DShading = True
                        .Caption = ""
                    End With
                End With
            End If
        Next x
    End If
    Exit For
Next y

 

[Jeester]

I haven't got excel installed at home, but try this.

.

Hey, Thanks for that. It worked really well for the first input but then there was nothing for any subsequent inputs, not even the heading. I don't seem to be able to find a reason why it wouldn't. :/

It seems to be because when I have something in Cell(5,y) it just jumps straight to Exit For.

 

[Jeester]

I have solved it using some of trunten's answer:

Dim y As Integer
y = 4
Do While y < 7
If IsEmpty(ws2.Cells(5, y)) Then
ws2.Cells(5, y).Value = Me.IntName.Value


        For x = 7 To 150
            If Not IsEmpty(ws2.Cells(x, 3)) And ws2.Cells(x, 3).Value <> "EHCP" _
                    And ws2.Cells(x, 3).Value <> "SEN SUPPORT" _
                    And ws2.Cells(x, 3).Value <> "MONITOR" Then
                With ws2.Cells(x, y)
                    With ws2.CheckBoxes.Add(.Left, .Top, .Width, .Height)
                        .Value = xlOff
                        .LinkedCell = Cells(x, y).Address
                        .Display3DShading = True
                        .Caption = ""
                    End With
                End With
            End If
        Next x
Exit Do
Else
y = y + 1
End If


Loop