[VBA] Ending a loop after something has changed.

Jeester

New Member
Joined
Sep 6, 2014
Messages
17
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:
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
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I haven't got excel installed at home, but try this.
Code:
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
 
Upvote 0
I haven't got excel installed at home, but try this.
Code:
.
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.
 
Last edited:
Upvote 0
I have solved it using some of trunten's answer:

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

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top