Do Until Loop strange behavior
 

Hi,

I've written a Do Until loop that looks at whether a cell contains any info.
The Do until begins:

Do Until cells(X, 2) = ""

The problem is the loop when left to run stops half way through. However if
you step through it works fine.

Heres the loop code for info

Can anyone explain to me how to stop this behavior?

X = 6
Do Until Cells(X, 2) = ""
On Error GoTo ErrHand2
Sheets("SRS").Select
Set WTBFIND = Nothing
Set addrow = Nothing
WTBFIND = Cells(X, 4)
If WTBFIND < "" Then
addrow = "C"
If InStr(1, Cells(X, 2), "Yard") Then
addrow = Yard
Else
If InStr(1, Cells(X, 2), "Direct") Then
addrow = Direct
End If
End If
Sheets("wtb").Select
Cells.Find(what:=WTBFIND, After:=ActiveCell, LookIn:=xlValue,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
addcol = ActiveCell.Row
If addrow = "C" Then
Results = Cells(addcol, Yard) + Cells(addcol, Direct)
Else
Results = Cells(addcol, addrow)
End If
Else
Results = "0"
End If
ErrHan2: Sheets("SRS").Cells(X, 13) = Results
X = X + 1
Loop

Sheets("Srs").Select

Exit Sub

ErrHand2:
Results = "0"
Resume ErrHan2
End Sub

Do Until Loop strange behavior
 

There's too much unknown for me (and I suspect for anyone) to help you.
Too many variables are undefined and too many values are unknown. Here
are a few generic pointers.

You are switching between two worksheets. You are also searching the
wtb worksheet starting with the activecell. It is not clear to me that
the activecell is correctly initialized at the start of the process.

You should also consider getting rid off the on error code. Let VBA
raise errors. It will help you debug the code.

Also, consider code that doesn't necessarily rely on
loop until <""
Instead, use the End() property of the cell object to simulate the
effect of a CTRL+(left,right,up,down) arrow move.

Finally, there is often little need to select and activate
sheets/cells. Much can be accomplished without such steps. For a
short intro to the subject see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I've written a Do Until loop that looks at whether a cell contains any info.
The Do until begins:

Do Until cells(X, 2) = ""

The problem is the loop when left to run stops half way through. However if
you step through it works fine.

Heres the loop code for info

Can anyone explain to me how to stop this behavior?

X = 6
Do Until Cells(X, 2) = ""
On Error GoTo ErrHand2
Sheets("SRS").Select
Set WTBFIND = Nothing
Set addrow = Nothing
WTBFIND = Cells(X, 4)
If WTBFIND < "" Then
addrow = "C"
If InStr(1, Cells(X, 2), "Yard") Then
addrow = Yard
Else
If InStr(1, Cells(X, 2), "Direct") Then
addrow = Direct
End If
End If
Sheets("wtb").Select
Cells.Find(what:=WTBFIND, After:=ActiveCell, LookIn:=xlValue,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
addcol = ActiveCell.Row
If addrow = "C" Then
Results = Cells(addcol, Yard) + Cells(addcol, Direct)
Else
Results = Cells(addcol, addrow)
End If
Else
Results = "0"
End If
ErrHan2: Sheets("SRS").Cells(X, 13) = Results
X = X + 1
Loop

Sheets("Srs").Select

Exit Sub

ErrHand2:
Results = "0"
Resume ErrHan2
End Sub

Do Until Loop strange behavior
 

This might be something like what you want.

Sub GGGG()

Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range
Dim yard As String, direct As String
Dim addrow As String
Dim WTBFIND As Range
Set sh = Sheets("SRS")
Set sh1 = Sheets("WTB")
yard = "D" ' ?????
direct = "E" ' ??????
x = 6
On Error GoTo ErrHand2
Do Until sh.Cells(x, 2) = ""
Set WTBFIND = Nothing
addrow = 0
Set WTBFIND = sh.Cells(x, 4)
If WTBFIND.Value < "" Then
addrow = "C"
If InStr(1, sh.Cells(x, 2), "Yard") Then
addrow = yard
ElseIf InStr(1, Cells(x, 2), "Direct") Then
addrow = direct
End If
Set rng = sh1.Cells.Find(what:=WTBFIND.Value, _
After:=sh1.Range("A1"), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
addcol = rng.Row
If addrow = "C" Then
Results = sh.Cells(addcol, yard) + Cells(addcol, direct)
Else
Results = Cells(addcol, addrow)
End If
Else
Results = "0"
End If
Else
Results = "0"
End If
ErrHan2:
sh.Cells(x, 13) = Results
x = x + 1
Loop



Exit Sub

ErrHand2:
MsgBox "In ErrHandler, err: " & Err.Number & vbNewLine _
& Err.Description
Results = "0"
Resume ErrHan2
End Sub

--
Regards,
Tom Ogilvy



"dht" wrote in message
...
Hi,

I've written a Do Until loop that looks at whether a cell contains any
info.
The Do until begins:

Do Until cells(X, 2) = ""

The problem is the loop when left to run stops half way through. However
if
you step through it works fine.

Heres the loop code for info

Can anyone explain to me how to stop this behavior?

X = 6
Do Until Cells(X, 2) = ""
On Error GoTo ErrHand2
Sheets("SRS").Select
Set WTBFIND = Nothing
Set addrow = Nothing
WTBFIND = Cells(X, 4)
If WTBFIND < "" Then
addrow = "C"
If InStr(1, Cells(X, 2), "Yard") Then
addrow = Yard
Else
If InStr(1, Cells(X, 2), "Direct") Then
addrow = Direct
End If
End If
Sheets("wtb").Select
Cells.Find(what:=WTBFIND, After:=ActiveCell, LookIn:=xlValue,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
addcol = ActiveCell.Row
If addrow = "C" Then
Results = Cells(addcol, Yard) + Cells(addcol, Direct)
Else
Results = Cells(addcol, addrow)
End If
Else
Results = "0"
End If
ErrHan2: Sheets("SRS").Cells(X, 13) = Results
X = X + 1
Loop

Sheets("Srs").Select

Exit Sub

ErrHand2:
Results = "0"
Resume ErrHan2
End Sub