Loop without do (nested IF functions)

[Ghris]

Hey!

I have the following IF statement nest:

If Range("P24").Value > 1 Then   
 If Range("M30").Value <= 1 Then
        Range("R9").Value = Range("R9").Value - 0.1
        Loop Until Range("M30").Value >= 1
  End If
        Else
Range("R9").Value = Range("R9").Value - 0.1
Loop Until Range("P10").Text = True
    End If

So if P24 is > 1, it should check if M30 is below 1. If that's the case, do -0.1 on R9. And keep looping until M30 is above 1.
If P24 not >1, it should just do R9 - 0.1 until P 10 is true.

Long story short, I get a Loop without do error. Have searched for a solution, but can't find the solution. Or I am not applying the solution in the right way at least.

 

[Michael M]

You need to start the code with DO UNTIL
and use LOOP at the end
See here for an example

Excel VBA Do Until Loop - Easy Excel Macros

 

[Ghris]

Something like this then? I need to leave, so can't test it atm. (shift is ending, ill check in 2 hours when i get home)

If Range("P24").Value > 1 Then    If Range("M30").Value <= 1 Then
        Range("R9").Value = Range("R9").Value - 0.1
        Do Until Range("M30").Value >= 1
        Loop
  End If
        Else
Range("R9").Value = Range("R9").Value - 0.1
Do Until Range("P10").Text = True
 Loop
    End If

 

[ukmikeb]

Hi

Try this :-

If Range("P24").Value > 1 Then   
 If Range("M30").Value <= 1 Then
        Do
              Range("R9").Value = Range("R9").Value - 0.1
        Loop Until Range("M30").Value >= 1
  End If
 Else
       Do
             Range("R9").Value = Range("R9").Value - 0.1
       Loop Until Range("P10").Text = True
 End If

But, if neither M30 nor P10 are dependent on the value in R9 this will result in an interminable loop. (Test in Break mode first)

hth

 

[Ghris]

Thanks ukmikeb! I believe the code works. But now I get the same error on another place. And I can't find the origin of the error. It gives the error on a End sub statement...

Please ignore my newbie code, but here is the code in total:

Sub DoIt()

   If Range("F10") < Range("G22") Then
       MsgBox "Harvest amount too low."
       Exit Sub
   End If


If Range("F10") > 9999 Then
       MsgBox "Amount of grams is high, please check if liters are correct."
       Exit Sub
   End If


If Range("P24") > 1 Then
    Sortharvest2
         End If


Application.ScreenUpdating = False
Application.CutCopyMode = False


Worksheets("settings").Unprotect "pass"
Worksheets("add").Unprotect "pass"


Copyuse


   If Range("O11") > Range("I22") Then
       MsgBox "You selected too much harvests to add."
transplant
       
       Exit Sub
   End If


Worksheets("settings").Range("K:AZ").EntireColumn.Hidden = False
[COLOR=#ff0000]Range("R9").Value = Range("O10").Value[/COLOR]
[COLOR=#ff0000]ActiveSheet.Range("R8:R24").Select[/COLOR]
[COLOR=#ff0000]Do[/COLOR]
[COLOR=#ff0000]startSearch[/COLOR]
[COLOR=#ff0000]If Range("P24").Value > 1 Then[/COLOR]
[COLOR=#ff0000] If Range("M30").Value <= 1 Then[/COLOR]
[COLOR=#ff0000]        Do[/COLOR]
[COLOR=#ff0000]              Range("R9").Value = Range("R9").Value - 0.1[/COLOR]
[COLOR=#ff0000]        Loop Until Range("M30").Value >= 1[/COLOR]
[COLOR=#ff0000]  End If[/COLOR]
[COLOR=#ff0000] Else[/COLOR]
[COLOR=#ff0000]       Do[/COLOR]
[COLOR=#ff0000]             Range("R9").Value = Range("R9").Value - 0.1[/COLOR]
[COLOR=#ff0000]       Loop Until Range("P10").Text = True[/COLOR]
[COLOR=#ff0000] End If[/COLOR]


Worksheets("add").Range("K:AZ").EntireColumn.Hidden = False


Application.DisplayAlerts = False
ActiveWorkbook.Sheets("settings").Activate
[COLOR=#ff0000]If Range("P24").Value > 1 Then[/COLOR]
[COLOR=#ff0000]If Range("O18").Value = 1 Then[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S8").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]
[COLOR=#ff0000]If Range("O19").Value = 1 Then[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S9").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]
[COLOR=#ff0000]If Range("O20").Value = 1 Then[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S10").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]
[COLOR=#ff0000]If Range("O21").Value = 1 Then[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S11").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]
[COLOR=#ff0000]If Range("O22").Value = 1 Then[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S12").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]
[COLOR=#ff0000]Else[/COLOR]
[COLOR=#ff0000]Worksheets("settings").Range("S8").Cells.Copy[/COLOR]
[COLOR=#ff0000]End If[/COLOR]


Worksheets("add").Range("O5").PasteSpecial
Worksheets("settings").Range("S8").Cells.Clear
Application.DisplayAlerts = True
textocol


Worksheets("settings").Range("K:AZ").EntireColumn.Hidden = True
Worksheets("add").Range("K:AZ").EntireColumn.Hidden = True
Worksheets("settings").Protect "pass"
Worksheets("add").Protect "pass"
Sortharvest1
ActiveWorkbook.Sheets("Harvest sorting").Activate
ActiveSheet.Range("C7").Select
finish
End Sub

I know it's messy work, and I could do some of it better now that I gained more knowledge, but first priority for me is to get it to work...

So the End Sub statement is where it gives the error now. Before your fix on my code it gave the error on the exact spot where it went wrong... Can't imagine there is something wrong with End Sub though..
The red parts are the parts I added recently.

 

[ukmikeb]

Hi

Do you have another sub called "Sortharvest2" which you execute with :-

If Range("P24") > 1 Then
    Sortharvest2
         End If

or do you mean :-

If Range("P24") > 1 Then
    Goto Sortharvest2
         End If

in which case you need the label statement to be :-

Sortharvest2:

note the required colon after the label name.

You have a few others like "Copyuse", "startSearch", "textocol", "Sortharvest1" and "finish" which should be label statements unless you have modules of that name.

Finally, your error on the End Sub is caused by the fact that you have a "Do" (third line of Red Text) that has not been terminated with a "Loop" or "While" statement.

hth

 

[Ghris]

Thanks a lot ukmikeb!
Are the GoTo <sub> and sub: necessary? I find I have no trouble with the way they are stated now. Maybe I could get into trouble if I accidentally name my subs as functions?

Secondly, that error on the end sub was indeed caused by the Do on the third line. That one has been replaced by the two other do's in the IF functions. Also the startsearch function was not yet moved. Had to add those after the do's in the IF functions as well. Now it works like a charm!

Again, thank so much, I really appreciate your efforts!