How to trap an error with onError in a for i loop

[Kevin]

I am pretty new to VBA in Excel and am working through some stuff that will
help me at work. I wrote some code that essentially looks at one file, finds
a value (inventory number in this case) in a column, goes over a few cells,
copies the cell (on hand value) and then finds that same inventory number in
another workbook and pastes it a few cells over in that workbook. It works
fine, but to get better at this, I want to be able to handle errors. I have
purposly sabotaged some of the cells so that it won't be able to find the
value it is looking forand will cause an error just to see what happend. I
use the "onError" event, but I am running a loop and it only works once, but
not if there are more than one error. I just want it to skip the
instructions between the OnError and the line it says to go to. Again, it
does actually work once, but the next time it just gives me the error again.
By the way, this repeats hundreds of times.

Here is some of the code, I know it could be more efficient, but you'll get
the point. What I want to know is how to make it skip a step everytime it
finds an error ,not just the first time.

'sets variable equal the total number of rows in the sheet
intRowCount = Range("A2").CurrentRegion.Rows.Count
'start loop to run through all the active cells
For i = 1 To intRowCount
'Sets the contents of the current cell as the variable
varItemNumber = ActiveCell.Value
'Moves 0 cells down and 18 cells right (where this data should be)
ActiveCell.Offset(0, 18).Select
'Copies the data from the cell
ActiveCell.Copy
'Switch to the other sheet where the data will be used
Windows("Daily Analysis for Bug Testing.xls").Activate
Sheets("Products").Select
'Selects the entire row of item numbers to "find" from
Columns("A:A").Select
'Finds the cell that contains the item number that was stored as
"varItemNumber"
On Error GoTo line1
Selection.Find(What:=varItemNumber).Activate
'Moves 6 spaces to the right
ActiveCell.Offset(0, 6).Select
'Pastes the data
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Goes back to the original sheet

line1:
Windows("Workbook.xls").Activate
Sheets("Data").Select
'moves one cell down and 2 cells to the left to pickup the next item
number
ActiveCell.Offset(1, -18).Select
'starts the loop all over again
Next i

Any help would be much appreciated.

 

[Jim Rech]

Every error handler must have a Resume statement unless you are immediately
exiting the sub. The general format is:

Sub MyMacro()


On Error GoTo ErrorHandler
For Counter = 1 to 10
'Do something
StartAgain:
Next
Exit Sub


ErrorHandler:
'Fix problem, etc.
Resume StartAgain
End Sub


While you are "in the error handler" you have no error handling. After the
Resume error handling will be back, you do not need an other On Error:


Sub MyBadMacro()
On Error GoTo ErrorHandler
For Counter = 1 To 10
x = 1 / 0
StartAgain:
Next
Exit Sub


ErrorHandler:
Debug.Print Counter
Resume StartAgain
End Sub


If you want to ignore the error or trap it 'in line' you can use On Error
Resume Next.

Sub InLineTrap()
On Error Resume Next
Err.Clear
x = 1 / 0
If Err.Number <> 0 Then
Debug.Print "Error: " & Err.Description
Err.Clear
End If
End Sub



--
Jim
|I am pretty new to VBA in Excel and am working through some stuff that will
| help me at work. I wrote some code that essentially looks at one file,
finds
| a value (inventory number in this case) in a column, goes over a few
cells,
| copies the cell (on hand value) and then finds that same inventory number
in
| another workbook and pastes it a few cells over in that workbook. It
works
| fine, but to get better at this, I want to be able to handle errors. I
have
| purposly sabotaged some of the cells so that it won't be able to find the
| value it is looking forand will cause an error just to see what happend.
I
| use the "onError" event, but I am running a loop and it only works once,
but
| not if there are more than one error. I just want it to skip the
| instructions between the OnError and the line it says to go to. Again, it
| does actually work once, but the next time it just gives me the error
again.
| By the way, this repeats hundreds of times.
|
| Here is some of the code, I know it could be more efficient, but you'll
get
| the point. What I want to know is how to make it skip a step everytime it
| finds an error ,not just the first time.
|
| 'sets variable equal the total number of rows in the sheet
| intRowCount = Range("A2").CurrentRegion.Rows.Count
| 'start loop to run through all the active cells
| For i = 1 To intRowCount
| 'Sets the contents of the current cell as the variable
| varItemNumber = ActiveCell.Value
| 'Moves 0 cells down and 18 cells right (where this data should be)
| ActiveCell.Offset(0, 18).Select
| 'Copies the data from the cell
| ActiveCell.Copy
| 'Switch to the other sheet where the data will be used
| Windows("Daily Analysis for Bug Testing.xls").Activate
| Sheets("Products").Select
| 'Selects the entire row of item numbers to "find" from
| Columns("A:A").Select
| 'Finds the cell that contains the item number that was stored as
| "varItemNumber"
| On Error GoTo line1
| Selection.Find(What:=varItemNumber).Activate
| 'Moves 6 spaces to the right
| ActiveCell.Offset(0, 6).Select
| 'Pastes the data
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| 'Goes back to the original sheet
|
| line1:
| Windows("Workbook.xls").Activate
| Sheets("Data").Select
| 'moves one cell down and 2 cells to the left to pickup the next item
| number
| ActiveCell.Offset(1, -18).Select
| 'starts the loop all over again
| Next i
|
| Any help would be much appreciated.
|
|

 

[Dave Peterson]

How about a slight modification...

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim OtherWks As Worksheet
Dim FoundCell As Range

With ActiveSheet
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set OtherWks = Workbooks("Daily Analysis for Bug Testing.xls") _
.Worksheets("Products")

For Each myCell In myRng.Cells
With OtherWks.Range("a:a")
Set FoundCell = .Cells.Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'it wasn't found
Else
'assigning the value is like
'edit|copy, edit|paste special values
FoundCell.Offset(0, 6).Value = myCell.Offset(0, 18).Value
End If
End With
Next myCell

End Sub

I can test a variable to see if the find was successful.

=====
but you could try adding:

Line1:
err.clear
'rest of code

to your original routine.

ps. You'll want to specify all the options on your .find statement. Excel and
VBA will remember whatever was last used--and it's better to specify those
options that to search for that kind of intermittent bug.