Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behavior | Excel Discussion (Misc queries) | |||
INDIRECT - Strange behavior | Excel Discussion (Misc queries) | |||
Excel Mac OS X - Strange Behavior | Excel Discussion (Misc queries) | |||
Strange behavior | Setting up and Configuration of Excel | |||
Strange behavior. | Excel Discussion (Misc queries) |