Error trapping not working

[jmootrey]

Hi guys, I am running a loop to grab values from a fixed position i
relation to a unique model name. Sometimes the user asks for models tha
do not exist. On this case the value (forecast) should = 0. T
accomplish this I wrote an error trap, and it works on the firs
instance but if the user should ask for a second non existant model th
seleciton.find command errors out with run time code 91.
---- CODE ---
Do While model <> ""
On Error GoTo notfound
Sheets("database").Columns("B:B").Select
Selection.Find(What:=model, LookAt:=xlWhole, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False).Select
forecast = ActiveCell.Offset(0, 7 + week)
continue:
Sheets("work").Range("A" & rowcount + 8) = model
Sheets("work").Range("B" & rowcount + 8) = forecast
model = Sheets("data").Range("A" & modelcount)
rowcount = rowcount + 1
modelcount = modelcount + 1
Loop
Sheets("work").Activate
End If
Exit Sub
notfound:
forecast = 0
GoTo continue
End Sub

 

[Dave Peterson]

You could try clearing the error in your notfound: portion:

notfound:
forecast = 0
err.clear
GoTo continue

But I wouldn't do this, I'd use a variable that can be examined to see if the
..find was successful:


Dim FoundCell as range

Do While model <> ""
with Sheets("database")
with .Columns("B:B")
set foundcell = .cells.find(what:=model, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
'do nothing???
forecast = -99999 'some indicator???
else
'should you check to see if it's numeric first???
forecast = foundcell.offset(0, 7 + week).value
end if

with workSheets("work")
.Range("A" & rowcount + 8) = model
if forecast = -99999 then
.range("B" & rowcount + 8) = "Invalid??"
else
.Range("B" & rowcount + 8) = forecast
end if
model = .Range("A" & modelcount)
end with

rowcount = rowcount + 1
modelcount = modelcount + 1

Loop

end sub

If you wanted the avoid the changes to the Work worksheet, then you may want:

Dim FoundCell as range

Do While model <> ""
with Sheets("database")
with .Columns("B:B")
set foundcell = .cells.find(what:=model, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
'do nothing???
forecast = -99999 'some indicator???
else
'should you check to see if it's numeric first???
forecast = foundcell.offset(0, 7 + week).value

with workSheets("work")
.Range("A" & rowcount + 8) = model
if forecast = -99999 then
.range("B" & rowcount + 8) = "Invalid??"
else
.Range("B" & rowcount + 8) = forecast
end if
model = .Range("A" & modelcount)
end with

rowcount = rowcount + 1
modelcount = modelcount + 1

end if

Loop

end sub

(Both are untested, uncompiled.)

Hi guys, I am running a loop to grab values from a fixed position in
relation to a unique model name. Sometimes the user asks for models that
do not exist. On this case the value (forecast) should = 0. To
accomplish this I wrote an error trap, and it works on the first
instance but if the user should ask for a second non existant model the
seleciton.find command errors out with run time code 91.
---- CODE ---
Do While model <> ""
On Error GoTo notfound
Sheets("database").Columns("B:B").Select
Selection.Find(What:=model, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Select
forecast = ActiveCell.Offset(0, 7 + week)
continue:
Sheets("work").Range("A" & rowcount + 8) = model
Sheets("work").Range("B" & rowcount + 8) = forecast
model = Sheets("data").Range("A" & modelcount)
rowcount = rowcount + 1
modelcount = modelcount + 1
Loop
Sheets("work").Activate
End If
Exit Sub
notfound:
forecast = 0
GoTo continue
End Sub

--
jmootrey
------------------------------------------------------------------------
jmootrey's Profile: 1387
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=184562

Microsoft Office Help