Loop Structure Recommendations

[SBF12345]

Greetings,

I would like to write a conditional loop that upon meeting the condition(s) will produce a set of variable values to be placed in another sheet.

I would like to be able to produce variables for all instances when a cell value associated with a row (and the counter variable in the loop) is below a threshold, BUT then also produce variable values for the FIRST INSTANCE when the threshold is overcome.

How can I structure a loop so that variable values will be assigned up to the point the threshold is overcome AND at the first point the threshold is overcome?

I am looking for a binary value to be assigned to one of the variables at the moment the threshold is overcome.

 

[Yongle]

try something along these lines..
- for illustration each value in an array of values checked for values below 6
- value written to string up to and including first time condition is berached
- you can apply the same method to loop and check values in a worksheet range

Sub Test()
    Dim arr As Variant, a As Long, myStr As String
    arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
    For a = 0 To UBound(arr)
        If arr(a) < 6 Then      [COLOR=#006400][I]'write to sheet[/I][/COLOR]
            myStr = myStr & vbCr & "below" & vbTab & arr(a)
        Else [I][COLOR=#006400] 'threshold overcome - write first value to sheet and exit the loop[/COLOR][/I]
            myStr = myStr & vbCr & "breach" & vbTab & arr(a)
            Exit For
        End If
    Next a
    MsgBox myStr
End Sub

 

[SBF12345]

Thanks!...I still need to fill in bits and pieces but this is the structure I'm looking for

If C = "P_HORIZONTAL" Then

        Arr() = PBK.Range(Cells(F, "E"), Cells(2, "E"))
        
        For H = 1 To UBound(Arr)
            
            If E - Arr(H) > 0 Then  'if the resistence trend is below trend write X's and Y to sheet
            
            'calculate X's and Y and write
            
            ElseIf E - Arr(H) <= 0 Then
            
            'calculate X's and Y and write to sheet then exit loop, also redim Arr
            
            End If
        
        
    ElseIf C = "T_HORIZONTAL" Then
    
        Arr() = PBK.Range(Cells(F, "E"), Cells(2, "E"))
    
    End If

 

[SBF12345]

Related Question:

I'd like to be able to incorporate an array with additional columns:

Arr() = PBK.Range(Cells(F, "A"), Cells(2, "E"))        
        For H = 1 To UBound(Arr)

How can I write a counter variable into the loop that uses Arr() as the basis for the counter? Now with two potential subscript values which does UBound use?