Issue with Do Loop

Hi - I'm having trouble understanding why the loop below is failing -- when I
execute the code I get the following error: "Compile error: Loop without
Do".

Any help is appreciated!! The code is below:

Sub thirdlevel()

Dim thirdvar 'variable for "3rd Level Groundwater" sheet
Dim copyvar 'variable for "Copy of Data for Graphs" sheet

'Checking "3rd Level Groundwater" sheet to see how many previous entries
there were
Sheets("3rd Level Groundwater (2)").Select
Range("E5").Select

thirdvar = 0

Do
If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
Exit Do
Else
thirdvar = thirdvar + 1
Loop
End If

Hi Pivot,

You need to Place the loop statement after End If:

Sub thirdlevel()

Dim thirdvar 'variable for "3rd Level
'Groundwater" sheet

Dim copyvar 'variable for "Copy of Data
'for Graphs" sheet

' Checking "3rd Level Groundwater" sheet to see
' how many previous entries there were

Sheets("3rd Level Groundwater (2)").Select
Range("E5").Select

thirdvar = 0

Do
If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
Exit Do
Else
thirdvar = thirdvar + 1

End If
Loop

End Sub


---
Regards,
Norman



"Linking to specific cells in pivot table"
<Linkingtospecificcellsinpivottable@discussions.microsoft.com> wrote in
message news:4605BE53-E53D-40AB-9FDC-EE05B9CA150D@microsoft.com...
> Hi - I'm having trouble understanding why the loop below is failing --
> when I
> execute the code I get the following error: "Compile error: Loop without
> Do".
>
> Any help is appreciated!! The code is below:
>
> Sub thirdlevel()
>
> Dim thirdvar 'variable for "3rd Level Groundwater" sheet
> Dim copyvar 'variable for "Copy of Data for Graphs" sheet
>
> 'Checking "3rd Level Groundwater" sheet to see how many previous entries
> there were
> Sheets("3rd Level Groundwater (2)").Select
> Range("E5").Select
>
> thirdvar = 0
>
> Do
> If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
> Exit Do
> Else
> thirdvar = thirdvar + 1
> Loop
> End If
>
>

Control structures like Do...Loop and If Then...Else...End If must be
nested - completely enclosed by other control structures. So you could
use:

Do
If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
Exit Do
Else
thirdvar = thirdvar + 1
End If
Loop

But there's really no reason to use the Exit Do. Try:

Do Until Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0
thirdvar = thirdvar + 1
Loop


In article <4605BE53-E53D-40AB-9FDC-EE05B9CA150D@microsoft.com>,
"Linking to specific cells in pivot table"
<Linkingtospecificcellsinpivottable@discussions.microsoft.com> wrote:

> Hi - I'm having trouble understanding why the loop below is failing -- when I
> execute the code I get the following error: "Compile error: Loop without
> Do".
>
> Any help is appreciated!! The code is below:
>
> Sub thirdlevel()
>
> Dim thirdvar 'variable for "3rd Level Groundwater" sheet
> Dim copyvar 'variable for "Copy of Data for Graphs" sheet
>
> 'Checking "3rd Level Groundwater" sheet to see how many previous entries
> there were
> Sheets("3rd Level Groundwater (2)").Select
> Range("E5").Select
>
> thirdvar = 0
>
> Do
> If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
> Exit Do
> Else
> thirdvar = thirdvar + 1
> Loop
> End If
>
>

Thanks Norman and JE!! This is what I needed.

"JE McGimpsey" wrote:

> Control structures like Do...Loop and If Then...Else...End If must be
> nested - completely enclosed by other control structures. So you could
> use:
>
> Do
> If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
> Exit Do
> Else
> thirdvar = thirdvar + 1
> End If
> Loop
>
> But there's really no reason to use the Exit Do. Try:
>
> Do Until Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0
> thirdvar = thirdvar + 1
> Loop
>
>
> In article <4605BE53-E53D-40AB-9FDC-EE05B9CA150D@microsoft.com>,
> "Linking to specific cells in pivot table"
> <Linkingtospecificcellsinpivottable@discussions.microsoft.com> wrote:
>
> > Hi - I'm having trouble understanding why the loop below is failing -- when I
> > execute the code I get the following error: "Compile error: Loop without
> > Do".
> >
> > Any help is appreciated!! The code is below:
> >
> > Sub thirdlevel()
> >
> > Dim thirdvar 'variable for "3rd Level Groundwater" sheet
> > Dim copyvar 'variable for "Copy of Data for Graphs" sheet
> >
> > 'Checking "3rd Level Groundwater" sheet to see how many previous entries
> > there were
> > Sheets("3rd Level Groundwater (2)").Select
> > Range("E5").Select
> >
> > thirdvar = 0
> >
> > Do
> > If Len(ActiveCell.Offset(4 * thirdvar, 0)) = 0 Then
> > Exit Do
> > Else
> > thirdvar = thirdvar + 1
> > Loop
> > End If
> >
> >
>