Excel macro loop until the condition become true or false
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel macro loop until the condition become true or false
You haven't answered the question in my previous reply yet.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
There 10 rows but don't make the code for 10 rows
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel macro loop until the condition become true or false
This isn't going to work.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
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
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
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
Ok suppose we have 2 rows so how we do it
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel macro loop until the condition become true or false
See if this does what you want. The code is in Module1.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
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
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
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
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
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
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: Excel macro loop until the condition become true or false
>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
>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
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: Excel macro loop until the condition become true or false
OK - so why do you need a loop? That's what I am trying to understand.
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
i want to show you a video of my problem.
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: Excel macro loop until the condition become true or false
>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.
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.
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
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
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
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: Excel macro loop until the condition become true or false
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
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"
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
Thanks SpeakEasy it works.
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
How to combine these two loops to work together
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
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
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel VBA Function to concatenate results separated by comma
A variant of this question was also asked at How to Add these condition to this code
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Excel macro loop until the condition become true or false
I GOT THE SOLUTION