How to execute each statement in a For..Next Loop using an Error Handler to provide default values where there is error?

[TheBlueMusketeer]

Excel Gurus,
I need your help with this one. I have a For..Next loop using which I want to search for strings stored in an array within a particular sheet. Basically I am looking for the row location of each string in the array.

For example:

Sub DataCollect()
On Error Goto ErrHandler
'....Assume I used a loop to input 100 different strings into an array called "Array"
For i = 1 to 100

Row1 = Cells.Find(Array(i), SearchOrder:=xlbyRows, SearchDirection:=xlNext).Row

Sum1 = Sum1 + Cells(Row1, 3)

Next i

'This is what I need: If Array(i) is not in the sheet, I want to use the error handler to set the value of Sum1 as:
'Sum1 = Sum1 + 0
'and continue with the loop. Please advise as to how I can accomplish that. Doing the following, exits the sub. I want the loop to work as normal in case the searched value is not to be found.

Exit Sub
ErrHandler:
Sum1 = Sum1 + 0

End Sub
End

Thanks for your help,
S
Excel 2007

PS: I used On Error Resume Next but it screws up my program as I have some intermediate code in between.

 

[tusharm]

One way to do this is

    Row1 = 0
    On Error Resume Next
    Row1 = Cells.Find(Array(i), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    On Error GoTo 0
    If Row1 <> 0 Then Sum1 = Sum1 + Cells(Row1, 3)

Another way, with an implicit error trap:

    Dim FoundCell As Range
    Set FoundCell = Cells.Find(Array(i), SearchOrder:=xlByRows, SearchDirection:=xlNext)
    If Not FoundCell Is Nothing Then _
        Sum1 = Sum1 + Cells(FoundCell.Row, 3)

And, dispensing with the intermediate variable altogether:

    On Error Resume Next
    Sum1 = Sum1 + Cells(Cells.Find(Array(i), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row, 3)
    On Error GoTo 0

Excel Gurus,
I need your help with this one. I have a For..Next loop using which I want to search for strings stored in an array within a particular sheet. Basically I am looking for the row location of each string in the array.

For example:

Sub DataCollect()
On Error Goto ErrHandler
'....Assume I used a loop to input 100 different strings into an array called "Array"
For i = 1 to 100

Row1 = Cells.Find(Array(i), SearchOrder:=xlbyRows, SearchDirection:=xlNext).Row

Sum1 = Sum1 + Cells(Row1, 3)

Next i

'This is what I need: If Array(i) is not in the sheet, I want to use the error handler to set the value of Sum1 as:
'Sum1 = Sum1 + 0
'and continue with the loop. Please advise as to how I can accomplish that. Doing the following, exits the sub. I want the loop to work as normal in case the searched value is not to be found.

Exit Sub
ErrHandler:
Sum1 = Sum1 + 0

End Sub
End

Thanks for your help,
S
Excel 2007

PS: I used On Error Resume Next but it screws up my program as I have some intermediate code in between.

 

[TheBlueMusketeer]

Tushar Sir, you are the best. I used the implicit error trap and it worked perfectly. Thanks a bunch!

S

 

[tusharm]

You are welcome.

Tushar Sir, you are the best. I used the implicit error trap and it worked perfectly. Thanks a bunch!

S