on error goto trouble

[Guest]

Hi all,

i have the following vba code:

<BEGIN VBA CODE>
Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
addRange As Range, prevRange As Range
Dim numberOfProducts As Integer, position As Integer
Dim whsName As String, addCodeBox1 As String, addLotBox1 As String

On Error GoTo isAfter
Set prevRange = addRange
Set addRange =
foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
Range(foundRange1, foundRange2), 0), 0)
GoTo continue
setRange:
Set prevRange = addRange
Set addRange = foundRange1
GoTo continue

isAfter:
On Error GoTo 0
On Error GoTo isBefore
Set addRange =
foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
Range(foundRange1, foundRange2), -1) - 1, 0)
On Error GoTo 0
Resume continue

isBefore:
Set addRange =
foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
Range(foundRange1, foundRange2), 0), 0)
On Error GoTo 0
Resume continue
<END VBA CODE>

what this code is suppsed to do is set the addRange to the location where i
need to insert my line in my sheet. it looks in the found range
(foundRange1:foundRange2)
using the Match function. the first test is using match(0), to see if there
is an exact match. if that errors out, then try the isBefore version
match(-1) version, and if that errors out then try the isAfter version

when there is nothing in the list it runs fine and puts everything where it
is supposed to.
on the second run through, if the code# that is being looked for is 'less
than' the code# that is there, it will error on the first set addRange and
goto isAfter like it is supposed to.
then i will get an error message :
Run-Time error '1004':
Unable to get the Match property of the WorksheetFunction class

this error is on the 3rd line in isAfter: (the set addRange)
isn't the 'on error goto' supposed to handle this and goto isBefore?
(if i move the cursor to the first line in isBefore, the code will run ok
and everything goes where it should.

what am i missing about error trapping?

(btw, if the code# that is being matched is 'greater' than the code# that
exists, the code works ok w/o any VB errors - the error trapping seems to
work.)

 

[Guest]

IMHO its better to use a "safe" function than to trap errors this way

eg

insead of

ON ERROR GOTO ERROR_HANDLER
'''' code
''' more code

ON ERROR RESUME NEXT
matchnum = worksheetsunction.Match(what,where,false)
IF ERR.NUMBER = 0 THEN
' OK
ELSE
' No Match - error trapped
ERR.CLEAR
END IF
' reset the error handler
ON ERROR GOTO ERROR_HANDLER


The following is a nicer solution:

ON ERROR GOTO ERROR_HANDLER
'''' code
''' more code
matchnum = SafeMatch(what, where)
IF matchnum = 0 THEN
' etc

no need to break error handlers in your main code

Private Function SafeMatch(what as string, where as range) as long
On Error Resume Next
SafeMatch = worksheetsunction.Match(what,where,false)
On Error Goto 0
End Function


Also, and its contentious, but you should avoid using GOTO - and no, lets
not get into another long thread on this

 

[Dave Peterson]

Another way is to drop the .worksheetfunction and use application.match. It
returns an error value that you can test.

dim res as Variant 'can be an error
res = application.match(addcodebox1, range(foundrange1,foundrange2),0)
'now check the error
if iserror(res) then
'do what you want with the error
else
set addRange = foundrange1.offset(res,0)
end if

 

[Guest]

Hi Patrick,

I see your point. However, I don't think that will work in my case - I
tried it and i still get the RunTime Error '1004' (maybe i am doing something
wrong still)
i don't know if it makes a difference, but my 'codes' are not numbers, they
are a mix of letters and numbers, but always XXYYY or XXYYY/1, where X is a
letter and Y is a number.

i do have break on unhandled erros only checked in the options

would i be better off adding my code and the line of information to the last
spot, and then using the sort feature of excel to sort it for me?

 

[Guest]

thanks to all, the application.match seemed to work the best, but let me run
this by you...

is there a better way to find where the code# is supposed to go and insert it?

i tried inserting after the last code# and then doing a sort, but i have
formulas down the row that get messed up - absolute column, relative row -
and they need to be relative rows - which is why i wanted to figure out where
to put the new code# first before doing anything else.

thanks!

J

again the code numbers are of the format XXYYY or XXYYY/1 where X is a
letter and Y is a number (ie FC112, FL110, FL104/1, CO123, VE144, RU140)

 

[Guest]

match returns a number, or an error if theres no match.