Creating a do while loop

[vba317]

After over a month of working on this problem I have come to half a solution. Now I am hoping for some help in designing the rest of the solution. I have created three columns based on criteria from different loops.
I believe (Thou I may be wrong) that what I need to do is create a Do while loop.

I have information in the I column.
If a cell in the I column is greater than 0 I want a loop to go through the D column to see if any value in the D column is less than the value in the H column. If any value is less than I want a message in the J column. I want the loop to stop if the G column is greater than 0.

What I have so far is:

For R2 = 4 To LR
        
         Retrace1 = 0.03
         Retrace2 = Retrace1 * 100
         'Retracement Amount
         BUY = (Range("H" & R2) * Retrace1)
         'Retracement value
         BUY1 = Range("H" & R2) + BUY
         'Low Value
         BUY2 = Range("D" & R2)
         'Buy Amount
         BUY3 = BUY2 - Range("J" & R2)
        
        Range("I1").Value = Retrace2 & "% Retracement"
        If Range("H" & R2).Value > 0 Then Range("I" & R2).Value = BUY1
                       
         Next R2
         
                   
          
          Do While Range("H" & R2) > 0 And Range("G" & R2) < 0
        
            If Range("H" & R2 + 1).Value = 0 And BUY1 < BUY2 Then Range("J" & R3).Value = ("Buy at " & Format(BUY3, "0.00") & " or less")
        
        
         Loop

 

[macropod]

Hi vba317,

If a cell in the I column is greater than 0 I want a loop to go through the D column to see if any value in the D column is less than the value in the H column. If any value is less than I want a message in the J column. I want the loop to stop if the G column is greater than 0.

So, assuming you want to test column I against D11000:

Sub Test()
Dim oCel As Range, oRng As Range, Message As String
Message = "My Message"
Set oRng = Worksheets("Sheet1").Range("D1:D1000")
With ActiveSheet
  For Each oCel In .Columns(9).Cells
    If oCel.Value > 0 Then
      If WorksheetFunction.Min(oRng) < oCel.Offset(0, -1).Value Then _
        oCel.Offset(0, 1).Value = Message
      If oCel.Offset(0, -2).Value > 0 Then End
    End If
  Next
End With
End Sub

Cheers
[MS MVP - Word]

 

[vba317]

When I tried this code I got :
Do you know how I can resolve this error?
runtime error 9
subscript out of range

This line gets highlighted

[Yellow]Set oRng = Worksheets("Sheet1").Range("D1:D1000")[/Yellow]

 

[macropod]

Hi vba317,

As you can see from the code, it looks for the range D11000 on the worksheet named 'Sheet1'. You need to use the correct sheet name for your workbook. Clearly, if you don't have a worksheet with that name, the code won't work.



Cheers
[MS MVP - Word]