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

Gixxer_J_97 wrote:

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.M atch(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.M atch(addCodeBox1,
Range(foundRange1, foundRange2), -1) - 1, 0)
On Error GoTo 0
Resume continue

isBefo
Set addRange =
foundRange1.Offset(Application.WorksheetFunction.M atch(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.)

--

Dave Peterson

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)

"Dave Peterson" wrote:

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

Gixxer_J_97 wrote:

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.M atch(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.M atch(addCodeBox1,
Range(foundRange1, foundRange2), -1) - 1, 0)
On Error GoTo 0
Resume continue

isBefo
Set addRange =
foundRange1.Offset(Application.WorksheetFunction.M atch(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.)

--

Dave Peterson