Excel macro loop until the condition become true or false

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

Excel macro loop until the condition become true or false

Post by Niranjanmeyda »

<table>
<tr><td>A</td><td>B</td></tr>
<tr><td>0</td><td>Zero</td></tr>
</table>
if B is equal to ZERO then loop A until its reach to +1 or -1 and if A = +1 then write Positive to column B and if A=-1 then write Negative to column B.
Stop the loop if A reach to +1 or -1.

COGICPENNY
2StarLounger
Posts: 115
Joined: 20 Mar 2018, 13:40

INTERACTIVE ORDER FORM

Post by COGICPENNY »

Niranjanmeyda,

We're not understanding what you're saying to do. Please guide us further.

Penny

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

Re: INTERACTIVE ORDER FORM

Post by HansV »

Niranjanmeyda,

Why did you post this here? Is it a new question?
Best wishes,
Hans

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

Excel macro loop until the condition become ture or false

Post by Niranjanmeyda »

because i don't know how to post new topic. can any body help me with this. yes this is new question
<table>
<tr><td>A</td><td>B</td></tr>
<tr><td>0</td><td>Zero</td></tr>
</table>
I have excel sheet with 2 column A and B. The value in column A changes every second and column B has Zero value written in English.
if B is equal to ZERO then loop A until its reach to +1 or -1 and if A = +1 then write Positive to column B and if A=-1 then write Negative to column B.
Stop the loop if A reach to +1 or -1.

User avatar
HansV
Administrator
Posts: 78233
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 »

There is a NEW TOPIC button above the list of topics in the forum (and also below it):

S0852.png

I have moved your question plus the replies below it to a new topic.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78233
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 »

By "loop" do you mean loop through the cells A2, A3, A4, ... etc., or loop in time?
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 »

No, I mean the column A value changes every second, and if column A value >= +1 then write Positive to column B and if column B value <= -1 then write negative to column B stop the loop if one of the condition becomes true. Start the loop if column B value is equal to Zero.

User avatar
HansV
Administrator
Posts: 78233
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 »

Can't you simply use a formula in B2?

=IF(A2<=-1,"Negative",If(A2>=1,"Positive","Zero"))
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 »

no, i want to do it with foreach in vba
let me explain in programming way
checking if column B = zero
column B is zero so we start the loop
loop started (
now inside loop
checking if column A = 1 or -1
if column A = -1 then writing negative on column B
)
stooping the loop because one condition becomes true.
Last edited by Niranjanmeyda on 29 Oct 2021, 14:56, edited 2 times in total.

User avatar
HansV
Administrator
Posts: 78233
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 »

I'm afraid I don't understand.
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 »

no, i want to do it with foreach in vba
let me explain in programming way
checking if column B = zero
column B is zero so we start the loop
loop started (
now inside loop
checking if column A = 1 or -1
if column A = -1 then writing negative on column B
)
stooping the loop because one condition becomes true.

User avatar
HansV
Administrator
Posts: 78233
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 »

I asked before if you wanted to loop through the rows, and you replied No. So I don't understand.
Please explain in explicit steps instead of in general terms.
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 »

do you have any other idea doing this without loop because this method =IF(A2<=-1,"Negative",If(A2>=1,"Positive","Zero")) won't stop if one of the statement become true. can't we loop until column B != Zero. Can do this by while loop

User avatar
HansV
Administrator
Posts: 78233
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 »

I'm sorry, if you can't explain in explicit terms what exactly you want, I cannot help you.
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 »

Do while Range("B1").Value = "Positive"
if Range("A1").Value = "1" Then Range ("B1") = "Positive"
Loop
how to stop infinity loop

User avatar
HansV
Administrator
Posts: 78233
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 could run this macro:

Code: Select all

Sub DoTheLoop()
    Do
        DoEvents
        If Range("A1").Value > 0 Then
            Range("B1").Value = "Positive"
            Exit Do
        ElseIf Range("A1").Value < 0 Then
            Range("B1").Value = "Negative"
            Exit Do
        End If
    Loop
End Sub
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 »

How to run these codes side by side
Sub DoTheLoop()
Do
DoEvents
If Range("A1").Value > 0 Then
Range("B1").Value = "Positive"
Exit Do
ElseIf Range("A1").Value < 0 Then
Range("B1").Value = "Negative"
Exit Do
End If
If Range("A2").Value < 0 Then
Range("B2").Value = "Negative"
Exit Do
ElseIf Range("A2").Value > 0 Then
Range("B2").Value = "Positive"
Exit Do
End If
Loop

End Sub
Last edited by Niranjanmeyda on 30 Oct 2021, 09:19, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78233
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 »

Will it be only row 1 and 2, or more rows?
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 try something by my self but the problem is result shows after the loop completed. i want to run multiple if statement run side by side without disturbing each other

Sub DoTheLoop()
Do
DoEvents
If Range("A1").Value > 0 Then
Range("B1").Value = "Positive"
If Range("A2").Value < 0 Then
Range("B2").Value = "Negative"
Exit Do
ElseIf Range("A1").Value < 0 Then
Range("B1").Value = "Negative"
ElseIf Range("A2").Value > 0 Then
Range("B2").Value = "Positive"
Endif
Endif
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 »

my another attempt
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