+ Reply to Thread
Results 1 to 4 of 4

Breaking loop if condition met

  1. #1
    Registered User
    Join Date
    01-29-2015
    Location
    xxx
    MS-Off Ver
    2010
    Posts
    5

    Breaking loop if condition met

    Hi,
    I'm trying to create a condition such that if the range is not equal to zero then a message box appears (which works ok). However, i also want another condition that if the range is equal to zero, then it deletes a specified range.
    At the moment, my problem is that if one part of the range is not equal to zero, then a message box appears - but if the rest of the range is zero, it continues to delete the specified range. I need it to not delete the specified range if any of the range is non zero i.e. the else condition is never realised if any of the range is non zero. Here is my code as it stands:


    Sub Check_checks()
    For Each r In Range("U7,U11,u84,u85,u161,u162, e231, e232")
    If r.Value <> 0 Then
    MsgBox "Check again"

    Else
    Range("U7:U9").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=54
    Range("U84:U85").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=69
    Range("U161:U162").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=69
    Range("C228:E232").Select
    Range("E232").Activate
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-120
    End If
    Next

    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Breaking loop if condition met

    As you loop through, instead of outputting the Msgbox or clearing the range, increment a counter for the condition. That is, r.Value<>0.

    When you complete the loop, check the counter. Depending on the count value, THEN output the Msgbox or clear the range.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Breaking loop if condition met

    You cam also use:
    Please Login or Register  to view this content.
    rather than individual entries. And you don't need
    Please Login or Register  to view this content.

    Regards, TMS

  4. #4
    Registered User
    Join Date
    01-29-2015
    Location
    xxx
    MS-Off Ver
    2010
    Posts
    5

    Re: Breaking loop if condition met

    Hi,
    Thanks for your ideas, I really like them. I'm not very good at implementing them though. I have set up a counter, but whenever there is a nonzero value in the range, the message popup comes up 8 times; exactly the number of values in the range that i'm checking. Do you know why this is?

    Sub Check_checks()
    Dim i, counter As Integer
    counter = 0
    For Each r In Range("U7,U11,u84,u85,u161,u162, e231, e232")
    If r.Value <> 0 Then
    counter = counter + 1

    End If

    If counter <> 0 Then
    MsgBox "Check again"
    Else

    Range("U7:U9").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=54
    Range("U84:U85").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=69
    Range("U161:U162").Select
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.SmallScroll Down:=69
    Range("C228:E232").Select
    Range("E232").Activate
    Selection.ClearContents
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-120
    End If
    Next

    End Sub


    thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find function in nested loop breaking down - not on first time through loop
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-14-2014, 09:32 AM
  2. Find function in nested loop breaking down - not on first time through loop
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2014, 04:59 PM
  3. LOOP formula breaking
    By donyc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-08-2012, 04:28 PM
  4. Breaking out of a loop
    By hojkoff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2010, 07:29 AM
  5. Breaking a Find Loop
    By Jmoo in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2010, 10:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1