Error Handling with a Match Function.

[Hari Prasadh]

Hi,

I want to do error handling when Im using Match function (last line in the
below code). Please note the code below is part of a bigger code.


What happens is that if -- ActiveCell.Value -- doesnt exist in the
ange --- ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" &
RownumberofLastBaseattribute) -- then I get an error saying
-- Run-time error 1004 unable to get the match property of the worksheet
function class. This happens EVEN AFTER I added an -- On error go to
errorreading: -- but somehow it doesnt work.
(What I wanted is that if there is an error then in the Activecell a comment
is added giving some warning.)

a) Why is my On Error not go to not working?

b) Also, how to specify in the code that Error handler is supposed to add
only a comment and after that the normal code execution should resume ?

Please guide me.

Sub MainActualUpcodes()

Dim NameOfOSWorkbook As String
Dim sh As Worksheet
Dim r As Integer
Dim opi As Integer
Dim lookingupsheetname As String
Dim RownumberofLastBaseattribute As Integer
Dim vlookuprowthroughMatch As String

NameOfOSWorkbook = "Open end data (OS).xls"
Application.ScreenUpdating = False
Workbooks(NameOfOSWorkbook).Activate

For Each sh In Workbooks(NameOfOSWorkbook).Worksheets
Worksheets(sh.Name).Activate


r = Range("A65536").End(xlUp).Row

opi = ThisWorkbook.Worksheets("Input Sheet").Range("M65536").End(xlUp).Row
lookingupsheetname = WorksheetFunction.VLookup(sh.Name,
ThisWorkbook.Worksheets("Input Sheet").Range("m7:n" & opi), 2, False)
RownumberofLastBaseattribute =
ThisWorkbook.Sheets(lookingupsheetname).Range("i65536").End(xlUp).Row

On error go to Errorreading:
vlookuprowthroughMatch = WorksheetFunction.Match(ActiveCell.Value,
ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" &
RownumberofLastBaseattribute), 0)

' some code
'
ErrorReading:

With ActiveCell.AddComment
.Visible = True
.Text Text:="Warning:" & Chr(10) & "The mentioned attribute
doesnt exist in the Base Upcode List " _
& Chr(10) & "Update the Base list and re-run the macro"
End With

'some code
'some code

Next sh

End sub


Thanks a lot,
Hari
India

 

[Tom Ogilvy]

Look in Excel VBA help at Resume and error handling. The reason it doesn't
work is that you have an error and then never leave error handing mode -
then on the next loop when you get an error, excel quits since you have an
error in your error handlers. You have to use resume in your error handler
to get out of error handling mode.


You don't need to use error handling to do what you want.



If you use

res = Application.Match( args)
if iserror(res) then
' write comment
end if

application match returns an error testable by iserror if you don't use the
worksheetfunction qualifier.

 

[Hari Prasadh]

Hi Tom,

I read Help File after ur suggestion and constructed my code for error
handler (iserror was causing problem as specified below) based on the
example provided there.

I have tried both the ways and Im doing wrong somewhere in both of them.

a) once I tried using error handlers only (without any iserror statement )

On Error GoTo errorreading:
vlookuprowthroughMatch = Application.Match(ActiveCell.Value,
ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" &
RownumberofLastBaseattribute), 0)

If vlookuprowthroughMatch <> "" Then
ActiveCell.Value =
ThisWorkbook.Sheets(lookingupsheetname).Cells(vlookuprowthroughMatch + 1,
"J")
ActiveCell.Offset(0, 1).Range("A1").Select
End If

errorreading:
With ActiveCell.AddComment
.Visible = True
.Text Text:="Warning:" & Chr(10) & "The mentioned attribute
doesnt exist in the Base Upcode List " _
& Chr(10) & "Update the Base list and re-run the macro"
End With
Resume Next

Whats happening is that a case which has not encountered error is also going
inside the error handling code and a comment is getting added. Also, after
the addition of the comment the code breaks. --Run time error '1004 -
Application defined or object defined error.--


b) This time i tried with iserror statement along with Application.Match
(error handling not included)


vlookuprowthroughMatch = Application.Match(ActiveCell.Value,
ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" &
RownumberofLastBaseattribute), 0)


If IsError(vlookuprowthroughMatch) Then

With ActiveCell.AddComment
.Visible = True
.Text Text:="Warning:" & Chr(10) & "The mentioned attribute
doesnt exist in the Base Upcode List " _
& Chr(10) & "Update the Base list and re-run the macro"
End With

ElseIf vlookuprowthroughMatch <> "" Then
ActiveCell.Value =
ThisWorkbook.Sheets(lookingupsheetname).Cells(vlookuprowthroughMatch + 1,
"J")
ActiveCell.Offset(0, 1).Range("A1").Select

End If

Now, if match doesnt find a *match* then I get a Run-time error 13 : Type
mismatch in the line -- vlookuprowthroughMatch =
Application.Match(ActiveCell.Value,
ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" &
RownumberofLastBaseattribute), 0)
Please note if match finds a Match then there is no problem.


Please guide me.


Thanks a lot,
Hari
India

 

[Tom Ogilvy]

when using the iserror approach,

vlookuprowthroughMatch should be dimensioned as Variant since it will either
contain a number or a variant.

It will never be equal to "" so that shouldn't even be a test.

Dim vlookuprowthroughMatch as Variant

vlookuprowthroughMatch = Application.Match(ActiveCell.Value, _
ThisWorkbook.Sheets(lookingupsheetname).Range("i2:i" & _
RownumberofLastBaseattribute), 0)


If IsError(vlookuprowthroughMatch) Then

With ActiveCell.AddComment
.Visible = True
.Text Text:="Warning:" & Chr(10) & "The mentioned attribute " &
_
"doesnt exist in the Base Upcode List " _
& Chr(10) & "Update the Base list and re-run the macro"
End With
Else
ActiveCell.Value = _
ThisWorkbook.Sheets(lookingupsheetname). _
Cells(vlookuprowthroughMatch + 1, "J")
ActiveCell.Offset(0, 1).Range("A1").Select

End If


For your error handler case, you have to explicitely not execute the error
handler

code
code
Exit sub
errhandler:


Resume Next
End Sub

You don't have any exit sub command above our error handler, so your code
falls through.

 

[Hari Prasadh]

Hi Tom,

Thnx a lot for your help. Im able to get working with Match function after
changing it to variant.

Also thanx for the Error Handler explanation. It took me some amount of
re-reading in order to appreciate the syntax.

Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi Tom,

I forgot to ask you this.

In the First reply of yours to this question you had mentioned that I dont
need error handling to do what I want

application match returns an error testable by iserror if you don't use the
worksheetfunction qualifier.

I accordingly changed it but forgot to ask you the essential difference
between Application.worksheetfunction.match and Application.Match. Why is it
that in the later case one doesnt get an error. (I consulted help --
Application Property , WorksheetFunction Object, WorksheetFunction Object
but it went above my head)


Actually I wrote a code

If Cells(z, qupcode + 3) = "" Then
Exit For
ElseIf Not (IsError(Application.worksheetfunction.Find("=", Cells(z,
qupcode + 3)))) Then
ActiveCell.Formula = "'= if( " & "&" & Cells(1, 2) & " = " &
Cells(z, "b") & " )" & ThisWorkbook.Sheets(lookingupsheetname).Cells(qupcode
+ 1, "A") & " = " & Cells(z, "d")
Else
ActiveCell.Formula = "'= if( " & "&" & Cells(1, 2) & " = " &
Cells(z, "b") & " )" & Cells(z, "d")
End If

And the code was blanking out at the point when it couldnt find a -- "=" --
and then I remembered your first post regarding not using worskheetfunction
qualifier. And when i changed it to -- Application.Find -- things were
smooth.

If possible please throw some light on the same.

Thanks a lot,
Hari
India