Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trap an error with onError in a for i loop
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trap an error with onError in a for i loop
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 "Kevin" wrote in message ... |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. | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trap an error with onError in a for i loop
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. Kevin wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleaner Error trap in Loop. | Excel Discussion (Misc queries) | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Trap | Excel Programming | |||
error trap | Excel Programming |