Error 1004

[Guest]

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]

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

 

[Tom Ogilvy]

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

 

[Guest]

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.

 

[Tom Ogilvy]

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)

 

[Tom Ogilvy]

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