Compile error with Do Until Loop query.

[shumba]

Hi,

In the procedure below a compile error message “ Loop without Do” is being generated in the first Do Until Loop structure. This procedure has been working well previously. The Excel workbook is receiving real time data from an online server.


Could this be an issue with the server or Excel itself?

Sub Analyse()
      Application.ScreenUpdating = False
      'checks if the traded value has exceeded the Set Value
      'and todays low is greater than yesterdays low
      'and todays volume is greater than the previous five days
      Sheets("Market Interface").Select
      Range("p4").Select
      Do Until IsEmpty(ActiveCell)
      If Selection.Offset(0, -4).Value > Selection.Offset(0, -3).Value And _
      Selection.Offset(0, 24).Value > 0 Then
      Selection.Offset(0, -2).Copy Sheets("Selections").Range("b:b").Cells(Rows.Count).End(xlUp).Offset(1, 0)
      Selection.Offset(1, 0).Select
      Loop
      
      'puts the time when the stock was selected into column B
      Sheets("Selections").Select
      Range("a9").Select
      Do Until IsEmpty(Selection.Offset(0, 1).Value)
      Range("a:a").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select
      If Not IsEmpty(Selection.Offset(0, 1)) Then Selection.Value = Now
      Loop
   
  '    Range("c9").Select
  '
  '    'copies the code to column C in preperation to download RTD
  '    Do Until IsEmpty(Selection.Offset(0, -1).Value)
  '    Range("c:c").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select
  '    If Not IsEmpty(Selection.Offset(0, -1)) Then Selection.Value = Range("A" & ActiveCell.Row)
  '    Loop
   
      'deletes the codes that are duplicated up the list
      Call Check_for_previous_occurance_of_code
  End Sub

Any help will be much appreciated.

Thanks. Rob.

 

[Norie]

Rob

I don't know how what you describe could have happened - the code as is just wouldn't compile because you are missing an End If within the first loop.

This will compile:

   Do Until IsEmpty(ActiveCell)
        If Selection.Offset(0, -4).Value > Selection.Offset(0, -3).Value And _
           Selection.Offset(0, 24).Value > 0 Then
            Selection.Offset(0, -2).Copy Sheets("Selections").Range("b:b").Cells(Rows.Count).End(xlUp).Offset(1, 0)
            Selection.Offset(1, 0).Select
        End If
    Loop

 

[Norie]

Oops, might have posted code that could send you into an endless loop.

The End If should be before the Offset.

I've also added a reference to the range, you don't really need to use Select.

 Set rng = Sheets("Market Interface").Range("p4")
    Do Until IsEmpty(rng)
        If rng.Offset(0, -4).Value > rng.Offset(0, -3).Value And rng.Offset(0, 24).Value > 0 Then
            rng.Offset(0, -2).Copy Sheets("Selections").Range("b:b").Cells(Rows.Count).End(xlUp).Offset(1, 0)
        End If
        Set rng = rng.Offset(1, 0)
    Loop