Excel - Nested Do While Loops

Asked By gu on 20-Nov-08 12:14 PM
I have been trying to get a nested Do While Loop and I need to find out where
I went wrong.  The snippet of the procedure is as follows:

Sub control()
Dim Target1 As Integer
Dim Target2 As Integer
Dim TTarget As Integer
Dim Net As Integer
Dim ExNet As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer


BTarget = Sheets("Sheet1").Range("H3")
STarget = Sheets("Sheet1").Range("H4")
ExNet = Sheets("Sheet1").Range("N3")
TTarget = Target1 + Target2
While TTarget <> 0 And i < 100
If TTarget > ExNet Then
Net = TTarget - ExNet
While Net > 0 And j < 100
Sheets("Sheet1").Range("G3") = 1
Sheets("Sheet1").Range("G4") = 0
j = j + 1
Wend
Else:
If ExNet > TTarget Then
Net = Exnet - TTarget
While Net > 0 And k < 100
Sheets("Sheet1").Range("G3") = 0
Sheets("Sheet1").Range("G4") = 1
k = k + 1
Wend
End If
i = i + 1
Wend
End If
End Sub

I placed the i,j, and k just to control the maximum times the loop occurs so
that it would not be indefinite.
I am getting errors with the 'Wend without While' compile error when the
above procedure is run.




Bob Phillips replied on 20-Nov-08 12:24 PM
Just fixing the syntax

Sub control()
Dim Target1 As Integer
Dim Target2 As Integer
Dim TTarget As Integer
Dim Net As Integer
Dim ExNet As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer


BTarget = Sheets("Sheet1").Range("H3")
STarget = Sheets("Sheet1").Range("H4")
ExNet = Sheets("Sheet1").Range("N3")
TTarget = Target1 + Target2
While TTarget <> 0 And i < 100

If TTarget > ExNet Then

Net = TTarget - ExNet
While Net > 0 And j < 100

Sheets("Sheet1").Range("G3") = 1
Sheets("Sheet1").Range("G4") = 0
j = j + 1
Wend
Else
If ExNet > TTarget Then

Net = ExNet - TTarget
While Net > 0 And k < 100

Sheets("Sheet1").Range("G3") = 0
Sheets("Sheet1").Range("G4") = 1
k = k + 1
Wend
End If

i = i + 1
End If
Wend
End Sub


--
__________________________________
HTH

Bob
Susan replied on 22-Nov-08 02:18 PM
i think your last "End If" should come before your last "Wend".
i added more spaces to your coding to indent it in a more exaggerated
manner & that's what i come up with.
susan


here
While TTarget <> 0 And i < 100
=A0 =A0 If TTarget > ExNet Then
=A0 =A0     Net =3D TTarget - ExNet
=A0 =A0           While Net > 0 And j < 100
=A0 =A0 =A0 =A0             Sheets("Sheet1").Range("G3") =3D 1
=A0 =A0 =A0 =A0             Sheets("Sheet1").Range("G4") =3D 0
=A0 =A0 =A0 =A0              j =3D j + 1
=A0 =A0            Wend
=A0 =A0 Else:
=A0 =A0        If ExNet > TTarget Then
=A0 =A0        Net =3D Exnet - TTarget
=A0 =A0             While Net > 0 And k < 100
=A0 =A0 =A0 =A0                Sheets("Sheet1").Range("G3") =3D 0
=A0 =A0 =A0 =A0                Sheets("Sheet1").Range("G4") =3D 1
=A0 =A0 =A0 =A0                k =3D k + 1
=A0 =A0             Wend
=A0 =A0         End If
i =3D i + 1
Wend
End If
End Sub
so
Dave Peterson replied on 20-Nov-08 12:28 PM
If you indent your code, it makes it easier to line up your while/wend and
if/else/end if's.



While TTarget <> 0 And i < 100
If TTarget > ExNet Then
Net = TTarget - ExNet
While Net > 0 And j < 100
Sheets("Sheet1").Range("G3") = 1
Sheets("Sheet1").Range("G4") = 0
j = j + 1
Wend
Else
If ExNet > TTarget Then
Net = Exnet - TTarget
While Net > 0 And k < 100
Sheets("Sheet1").Range("G3") = 0
Sheets("Sheet1").Range("G4") = 1
k = k + 1
Wend
End If
end if  '<-- added
i = i + 1
Wend


--

Dave Peterson