I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which works
great the first time through the loop but if a second lookup value does not
exist the error handling process does not get activated.


For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike

You didn't like a suggestion from yesterday?

If you drop the .worksheetfunction, you can test for the error:

dim mtch as Variant 'could return an error

mtch = Application.WorksheetFunction.VLookup( _
Worksheets("Nabanco").Cells(nr, 14).Value, _
Worksheets("RDMTemp").Range("A1:T5000"), _
20, False)

if iserror(mtch) then
'it returned an error
else
'no error
end if


MJRay wrote:

I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which works
great the first time through the loop but if a second lookup value does not
exist the error handling process does not get activated.

For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike

--

Dave Peterson

Dim mtch as Variant
For nr = 2 to lnr
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
if iserror(mtch) then
with Worksheets("NABTemp").Rows(nr)
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End With
End If
Else
' code to process match
End if

next nr

--
Regards,
Tom Ogilvy


"MJRay" wrote in message
...
I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which works
great the first time through the loop but if a second lookup value does
not
exist the error handling process does not get activated.


For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike

You don't have to use the vlookup function to do what you want to do. The
following function counts the number of occurances of a match, but you can
alter it to do whatever you like.

Function Count_Duplicates(Ip_Range As range, Cell_Chk As range) As Integer
Dim i As Integer, j As Integer, temp As Integer

' Function compares a cell (Cell_Chk) to a range
' (IP_Range) and returns the number of occurances
' of the cell value found in the range.

For j = 1 To Ip_Range.Columns.count
For i = 1 To Ip_Range.Rows.count
If Trim(Ip_Range(i, j)) = Trim(Cell_Chk(1, 1)) Then temp = temp
+ 1
Next i
Next j
Count_Duplicates = temp
End Function

The above function returns zero with no matches. You can avoid the returned
error.



"MJRay" wrote:

I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which works
great the first time through the loop but if a second lookup value does not
exist the error handling process does not get activated.


For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike

Did finish editing before sending:

Dim mtch as Variant
For nr = 2 to lnr
mtch = Application.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
if iserror(mtch) then
with Worksheets("NABTemp").Rows(nr)
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End With
End If
Else
' code to process match
End if

next nr

Since you are only using one column you could also use Match

mtch = Application.Match( _
snamt, ActiveSheet.Range("T1:T5000"),0)


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Dim mtch as Variant
For nr = 2 to lnr
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
if iserror(mtch) then
with Worksheets("NABTemp").Rows(nr)
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End With
End If
Else
' code to process match
End if

next nr

--
Regards,
Tom Ogilvy


"MJRay" wrote in message
...
I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which
works
great the first time through the loop but if a second lookup value does
not
exist the error handling process does not get activated.


For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike

Here is a simpler version (probably faster as well)

Function Count_Duplicates(Ip_Range As range, Cell_Chk As range) As Long
Dim i As Integer, j As Integer, temp As Integer

' Function compares a cell (Cell_Chk) to a range
' (IP_Range) and returns the number of occurances
' of the cell value found in the range.

Count_Duplicates = Application.Countif(IP_Range, _
"*" & Cell_Chk.Value & "*")

End Function

--
Regards,
Tom Ogilvy


"Chad" wrote in message
...
You don't have to use the vlookup function to do what you want to do. The
following function counts the number of occurances of a match, but you can
alter it to do whatever you like.

Function Count_Duplicates(Ip_Range As range, Cell_Chk As range) As Integer
Dim i As Integer, j As Integer, temp As Integer

' Function compares a cell (Cell_Chk) to a range
' (IP_Range) and returns the number of occurances
' of the cell value found in the range.

For j = 1 To Ip_Range.Columns.count
For i = 1 To Ip_Range.Rows.count
If Trim(Ip_Range(i, j)) = Trim(Cell_Chk(1, 1)) Then temp =
temp
+ 1
Next i
Next j
Count_Duplicates = temp
End Function

The above function returns zero with no matches. You can avoid the
returned
error.



"MJRay" wrote:

I have an error handling process within a For...next loop. If error 1004
occurs then the error handler gets activated. Once the error handling is
finished the next record in the loop is processed.

I am using a vlookup to match a value in a column. If the value does not
exist, I get error 1004. I put the following error handler in which
works
great the first time through the loop but if a second lookup value does
not
exist the error handling process does not get activated.


For nr = 2 to lnr
On Error GoTo errorhandler:
mtch = Application.WorksheetFunction.VLookup( _
snamt, ActiveSheet.Range("T1:T5000"), 1, False)
code to process if there is a match as a result of the lookup
errorhandler:
If Err = 1004 Then
Worksheets("NABTemp").Activate
Rows(nr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Err.Clear
End If
next nr

Thanks,
Mike