Excel macro loop until the condition become true or false

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel macro loop until the condition become true or false

Post by HansV »

You haven't answered the question in my previous reply yet.
Best wishes,
Hans

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

There 10 rows but don't make the code for 10 rows

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel macro loop until the condition become true or false

Post by HansV »

This isn't going to work.
Best wishes,
Hans

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

yes it will i just need little help you are expert in programing you can do this. The loop should be ends when both IF statement completed.
Sub DoTheLoop_2()

Do

DoEvents

FirstIf:

If Range("A1").Value > 0 Then

Range("B1").Value = "Positive"

GoTo NextTest

ElseIf Range("A1").Value < 0 Then

Range("B1").Value = "Negative"

GoTo NextTest

End If

NextTest:

If Range("A2").Value < 0 Then

Range("B2").Value = "Negative"

If Range("B1").Value <> ""

Exit Do

Else

GoTo FirstIf

ElseIf Range("A2").Value > 0 Then

Range("B2").Value = "Positive"

If Range("B1").Value <> ""

Exit Do

Else

GoTo FirstIf

End If

Loop

End Sub

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

Ok suppose we have 2 rows so how we do it

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel macro loop until the condition become true or false

Post by HansV »

See if this does what you want. The code is in Module1.

Test.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

what's wrong with this code. how to do this without button module and loop until last given row
For i = 1 To 1
If Range("B" & i).Value = "Zero" Then
If Worksheets("Sheet4").Range("A1").Value >= 10 Then
Worksheets("Sheet4").Range("B" & i).Value = "Positive"
ElseIf Worksheets("Sheet4").Range("A1").Value <= -10 Then
Worksheets("Sheet4").Range("B" & i).Value = "Negative"
End If
End If
Next i

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

how to stop loop after 1 To 10 completed
Sub start()

Do
For i = 1 To 10
If Range("B" & i).Value = "Zero" Then
If Range("A" & i).Value >= 1 Then
Range("B" & i).Value = "Positive"
ElseIf Range("A" & i).Value <= -1 Then
Range("B" & i).Value = "Negative"
End If
End If
Next i

DoEvents
Loop
End Sub

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Excel macro loop until the condition become true or false

Post by SpeakEasy »

>i want to do it with foreach in vba
>do you have any other idea doing this without loop

Ok, so which do you want - with a loop or without?

Given that you can achieve your desired output without a loop, indeed, without any VBA or macro code at alll

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

with loop

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Excel macro loop until the condition become true or false

Post by SpeakEasy »

OK - so why do you need a loop? That's what I am trying to understand.

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

i want to show you a video of my problem.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Excel macro loop until the condition become true or false

Post by SpeakEasy »

>i want to show you a video of my problem.

That may be possible, using the site's attachment facility, I guess.

But I think that I understand your problem, in that you don't know how to exit a tight loop within a tight loop (where your exit condition seems to that all rows of interest in Column B have been updated once and only once). Without it exiting, you remain in a tight loop, and Excel stops letting you do anything else ...

Of course you might not be having this problem if you were willing to entertain the idea that perhaps a loop is not necessarily the way to do this. To discuss possible alternatives we need to understand better your requirements,.

For example, are you actually only intending to capture the first change in column A (whether positive or negative). In other words if a cell in col A was negative and changes to zero or positive, are you sure that you don't want to capture that.?

An executive summary of your requirements (not a description of how you are currently trying to solve it) would be a big help.

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

This code work the way i want but the problem is after FOR loop completed Do loop keep running how to stop it
Sub start()



'Do

For i = 1 To 10

If Range("B" & i).Value = "Zero" Then

If Range("A" & i).Value >= 1 Then

Range("B" & i).Value = "Positive"

ElseIf Range("A" & i).Value <= -1 Then

Range("B" & i).Value = "Negative"

End If

End If

Next i



DoEvents

'Loop

End Sub

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Excel macro loop until the condition become true or false

Post by SpeakEasy »

There's a n old joke that goes something like this: a tourist in Ireland asks one of the locals for directions to Dublin. The Irishman replies: ‘Well sir, if I were you, I wouldn’t start from here’.

Which is where we are now. I wouldn't start from here ...

You state that 'This code work the way I want', but it doesn't, otherwise you wouldn't be here asking for help. I rather suspect that you don't actually need tight loops to get the result that you want, and the elimination of the loops would remove the requirement for exiting them (and DoEvents, which should only be used sparingly, frankly)

But you seem resolute in ignoring requests for further information, simply repeating the same info over and over again.

So here's a solution to your very specific example code. It's a rubbish solution, but it works

Code: Select all

Total = 0
Do
    For i = 1 To 10
        If Range("B" & i).Value = "Zero" Then
            If Range("A" & i).Value >= 1 Then
                Range("B" & i).Value = "Positive"
                Total = Total + 1
            ElseIf Range("A" & i).Value <= -1 Then
                Range("B" & i).Value = "Negative"
                Total = Total + 1
            End If
        End If
    Next i
    DoEvents
Loop Until Total = 10
MsgBox "Finished"

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

Thanks SpeakEasy it works.

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

How to combine these two loops to work together

Post by Niranjanmeyda »

Sub what()
Total = 0
Do

For i = 1 To 4
If Range("E" & i) = "Zero" Then
If Range("D" & i) >= 1 Then
Range("E" & i).Value = "Positive"
Total = Total + 1
End If
End If
Next i


DoEvents
Loop Until Total = 4

End Sub

I want to run this loop even if the above loop not completed.

Sub other()

Total = 0

Do

For j = 1 To 4
If Range("E" & j) = "Positive" Then
If Range("D" & j) <= -1 Then
Range("P" & j) = Range("A" & j).Value
Total = Total + 1
End If
End If
Next j

Loop Until Total = 2

End Sub

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel VBA Function to concatenate results separated by comma

Post by HansV »

A variant of this question was also asked at How to Add these condition to this code
Best wishes,
Hans

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

I GOT THE SOLUTION