[RESOLVED] Do While Loop Statement

[blueye89]

Hello everyone,
I started with making an macro and I need help.
At the bottom of the post is an image which represent what I want to do. I actually want to check value with every next two cells until condition become true.

Sub Workbook()


Sheets("sheet1").Activate
Range("F2:F11").Select
Selection.ClearContents

Dim i As Integer
Dim a As Double
Dim a1 As Double
Dim a2 As Double

For i = 2 To 11
a1 = Cells(i, 5).Value
a2 = Cells(i + 1, 5).Value
a = (Cells(i, 1).Value - Cells(i, 2).Value) / (Cells(i, 3).Value - Cells(i, 4).Value)

    If a1 < a < a2 Then
    Cells(i, 6).Value = a
    Else
        Do
        a = (Cells(i, 1).Value - Cells(i + 1, 2).Value) / (Cells(i, 3).Value - Cells(i + 1, 4).Value)
        Cells(i, 6).Value = a
        i = i + 1
        Loop While a1 > a And a> a2
    End If
Next

End Sub

[techgnome]

Well for starters you posted in the wrong forum. This is the .NET forum... there's a separate one for VBA help (Office DEvelopment) ... I'll ask the mods to move it for you so you don't need to create a new one.

-tg

[blueye89]

Well for starters you posted in the wrong forum. This is the .NET forum... there's a separate one for VBA help (Office DEvelopment) ... I'll ask the mods to move it for you so you don't need to create a new one.

-tg

Thank you very much.

[FunkyDexter]

Moved to Office.

[vbfbryce]

Is it resolved, or not? If not, what issue are you having exactly?

[blueye89]

Is it resolved, or not? If not, what issue are you having exactly?

Hello vbfbryce,
It is not yet resolved. I don't know who added in topic title "[RESOLVED]"

I have uploaded workbook that I am trying to create macro.

[vbfbryce]

Hmm...looked at the workbook, but I'm still fuzzy on what you're trying to do. I see how you're calculating A2', but don't see where A2 and A3 are coming from.

[blueye89]

Hmm...looked at the workbook, but I'm still fuzzy on what you're trying to do. I see how you're calculating A2', but don't see where A2 and A3 are coming from.

First five columns hava fixed values. I am getting from another source and values can't be changed.
There are too many rows but I used 10 rows for simplicity. When I realize that it works on few rows I will adapt on all other. The best way to realize what I want to do is to see the image named "equations".
Regards
Danijel

[vbfbryce]

Try stepping through something like this. Note that I'm not sure what you'd want to have happen if a row which is not the last row never meets the condition.

Sub loopCheck()
    Dim ws As Worksheet
    Dim j As Long
    Dim k As Long
    Dim lr As Long
    Dim a2prime As Double
    Dim Met As Boolean
    
    Met = False
    Set ws = ActiveSheet
    With ws
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row
        For j = 2 To lr
            If j = lr Then
                a2prime = (.Range("a" & j).Value - .Range("b" & j).Value) / (.Range("c" & j).Value - .Range("d" & j).Value)
                If .Range("e" & j).Value < a2prime And a2prime < .Range("e" & j).Value Then
                    .Range("f" & j).Value = a2prime
                    GoTo endIt
                Else
                    GoTo endIt
                End If
            End If
            k = j - 1
            Met = False
            a2prime = (.Range("a" & j).Value - .Range("b" & j).Value) / (.Range("c" & j).Value - .Range("d" & j).Value)
            
            While Met = False
                k = k + 1
                If k > lr Then
                    MsgBox "Condition not met for row " & j & ".  Exiting"
                    GoTo endIt
                End If
                If .Range("e" & k).Value < a2prime And a2prime < .Range("e" & k + 1).Value Then
                    .Range("f" & j).Value = a2prime
                    Met = True
                End If
            Wend
            
        Next j
    End With
endIt:
    Set ws = Nothing
End Sub

[blueye89]

Try stepping through something like this. Note that I'm not sure what you'd want to have happen if a row which is not the last row never meets the condition.

Sub loopCheck()
    Dim ws As Worksheet
    Dim j As Long
    Dim k As Long
    Dim lr As Long
    Dim a2prime As Double
    Dim Met As Boolean
    
    Met = False
    Set ws = ActiveSheet
    With ws
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row
        For j = 2 To lr
            If j = lr Then
                a2prime = (.Range("a" & j).Value - .Range("b" & j).Value) / (.Range("c" & j).Value - .Range("d" & j).Value)
                If .Range("e" & j).Value < a2prime And a2prime < .Range("e" & j).Value Then
                    .Range("f" & j).Value = a2prime
                    GoTo endIt
                Else
                    GoTo endIt
                End If
            End If
            k = j - 1
            Met = False
            a2prime = (.Range("a" & j).Value - .Range("b" & j).Value) / (.Range("c" & j).Value - .Range("d" & j).Value)
            
            While Met = False
                k = k + 1
                If k > lr Then
                    MsgBox "Condition not met for row " & j & ".  Exiting"
                    GoTo endIt
                End If
                If .Range("e" & k).Value < a2prime And a2prime < .Range("e" & k + 1).Value Then
                    .Range("f" & j).Value = a2prime
                    Met = True
                End If
            Wend
            
        Next j
    End With
endIt:
    Set ws = Nothing
End Sub

Hello vbfbryce,
This is usefull, I just need to clear all code lines that you wrote.
Thank you so much for this.
Best regards
blueye89

[vbfbryce]

You need to clear all code lines? What do you mean?

[blueye89]

You need to clear all code lines? What do you mean?

I thought to make it clear for me. I can't understand every line that you wrote cause I am not programmer.
Thank you very much.
Regads