VBA:: Exiting loops

Question

I am creating a search function that loops through a 1 dimensional array looking for answers specified by the user and my problem currently is that I only want the search to loop through the searched range once. However, it is currently stuck in the loop. Is there any suggestions on how I can exit this loop after it searches through the function once? I have currently the following code below. I am storing the cell addresses in the Results1() array and I only want each found term to be stored once

P1B1 is a radio button that specifies the function to search through the data range for any values less than the specified (Textbox1) value. FindRange1 is the range of data values I want the function to search through.

Dim Results1() As Variant
Dim Find1 As Range
Dim FindRange1 As Range
Dim TextBox1 As Long
Dim i1 As Long

            Set FindRange1 = Worksheets("Properties").Range("P7:P1000")
            If ILsearch.P1B1.Value = True Then
                For i1 = 0 To Max
                    For Each Find1 In FindRange1
                        If (Find1.Value < TextBox1) And (Find1.Value > 0) Then
                            i1 = i1 + 1
                            ReDim Preserve Results1(i1)
                            Results1(i1) = Find1.Address
                        End If
                    Next Find1
                Next i1
            End If

Anders Lindahl · Accepted Answer · 2011-06-27 09:47:24Z

1

Just get rid for the For i1=0 to Max loop - judging by your requirements it is not needed, as you only seem to want to visit every element in FindRange1 once.

answered Jun 27, 2011 at 9:47

Anders Lindahl

43.3k9 gold badges93 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reafidy Over a year ago

A solution would be to use Option Base 1

Mike Woodhouse · Accepted Answer · 2011-06-27 10:02:39Z

I think it's just a matter of only looping through the range, rather than looping through the array as well. Note that modifiying a loop variable (i1 in your code) is usually not a smart thing to do.

Dim Results1() As Variant
Dim Find1 As Range
Dim FindRange1 As Range
Dim TextBox1 As Long
Dim i1 As Long

    Set FindRange1 = Worksheets("Properties").Range("P7:P1000")
    If ILsearch.P1B1.Value = True Then
        i1 = 0
        For Each Find1 In FindRange1
            If (Find1.Value < TextBox1) And (Find1.Value > 0) Then
                i1 = i1 + 1                      ' increase results upper bound by 1
                ReDim Preserve Results1(1 to i1) ' extend array 
                Results1(i1) = Find1.Address     ' store the new found value
            End If
        Next
    End If

Mohammad Hosseinpour · Accepted Answer · 2024-09-03 09:48:33Z

-1

'A simple code is

For each x in CollectionX 'some commands If (Conditions for continue loop) then goto brPoint Endif 'some commands brPoint: Next

answered Sep 3, 2024 at 9:48

Mohammad Hosseinpour

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

VBA:: Exiting loops

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related