I have a list of sorted names (some repeat) and am trying to find the first
row a name occurs and the last row a name occurs. Sometimes the name
(DBName) is not in the list. When the name is not in the list, I get the
runtime error 13. I guess its because #N/A is being assigned to
DBRowNumFirst. How do I get around this problem?


Dim DBName As String
Dim DBRowNumFirst, DBRowNumLast, DBRows As Integer

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
DBRowNumFirst = Application.Match(DBName, Range("A3:A" +
CStr(DataRows)), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" + CStr(DataRows)),
1)

Even though DBRowNumFirst & DBRowNumLast are of type variant (not integer as
you might supect) they still will not accept error values. You could do is
something like this...

Dim DBName As String
Dim DBRowNumFirst as Long 'use long as int only holds 32k
Dim DBRowNumLast as Long 'and long is actually more efficent
Dim DBRows As Long

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
on error resume next
DBRowNumFirst = Application.Match(DBName, Range("A3:A" & DataRows), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" & DataRows), 1)
On error goto 0
if DBRowNumFirst = 0 or DBRowNumLast = 0 then msgbox "???"

--
HTH...

Jim Thomlinson


"Scott" wrote:

I have a list of sorted names (some repeat) and am trying to find the first
row a name occurs and the last row a name occurs. Sometimes the name
(DBName) is not in the list. When the name is not in the list, I get the
runtime error 13. I guess its because #N/A is being assigned to
DBRowNumFirst. How do I get around this problem?


Dim DBName As String
Dim DBRowNumFirst, DBRowNumLast, DBRows As Integer

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
DBRowNumFirst = Application.Match(DBName, Range("A3:A" +
CStr(DataRows)), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" + CStr(DataRows)),
1)

I also had this right above the code

On Error GoTo nodata

but it never goes there, I just get the runtime error 13 at
DBRowNumFirst = Application.Match(DBName, Range("A3:A" + CStr(DataRows)), 0)

I ran your example and it worked. In the debugger this is what it had

: DBRowNumFirst : Error 2042 : Variant/Error
--------------------------------------------------------------------------------------------------

"Jim Thomlinson" wrote:

Even though DBRowNumFirst & DBRowNumLast are of type variant (not integer as
you might supect) they still will not accept error values. You could do is
something like this...

Dim DBName As String
Dim DBRowNumFirst as Long 'use long as int only holds 32k
Dim DBRowNumLast as Long 'and long is actually more efficent
Dim DBRows As Long

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
on error resume next
DBRowNumFirst = Application.Match(DBName, Range("A3:A" & DataRows), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" & DataRows), 1)
On error goto 0
if DBRowNumFirst = 0 or DBRowNumLast = 0 then msgbox "???"

--
HTH...

Jim Thomlinson


"Scott" wrote:

I have a list of sorted names (some repeat) and am trying to find the first
row a name occurs and the last row a name occurs. Sometimes the name
(DBName) is not in the list. When the name is not in the list, I get the
runtime error 13. I guess its because #N/A is being assigned to
DBRowNumFirst. How do I get around this problem?


Dim DBName As String
Dim DBRowNumFirst, DBRowNumLast, DBRows As Integer

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
DBRowNumFirst = Application.Match(DBName, Range("A3:A" +
CStr(DataRows)), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" + CStr(DataRows)),
1)

I can not comment on code that is not posted as to why it might not be
working.
--
HTH...

Jim Thomlinson


"Scott" wrote:

I also had this right above the code

On Error GoTo nodata

but it never goes there, I just get the runtime error 13 at
DBRowNumFirst = Application.Match(DBName, Range("A3:A" + CStr(DataRows)), 0)

I ran your example and it worked. In the debugger this is what it had

: DBRowNumFirst : Error 2042 : Variant/Error
--------------------------------------------------------------------------------------------------

"Jim Thomlinson" wrote:

Even though DBRowNumFirst & DBRowNumLast are of type variant (not integer as
you might supect) they still will not accept error values. You could do is
something like this...

Dim DBName As String
Dim DBRowNumFirst as Long 'use long as int only holds 32k
Dim DBRowNumLast as Long 'and long is actually more efficent
Dim DBRows As Long

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
on error resume next
DBRowNumFirst = Application.Match(DBName, Range("A3:A" & DataRows), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" & DataRows), 1)
On error goto 0
if DBRowNumFirst = 0 or DBRowNumLast = 0 then msgbox "???"

--
HTH...

Jim Thomlinson


"Scott" wrote:

I have a list of sorted names (some repeat) and am trying to find the first
row a name occurs and the last row a name occurs. Sometimes the name
(DBName) is not in the list. When the name is not in the list, I get the
runtime error 13. I guess its because #N/A is being assigned to
DBRowNumFirst. How do I get around this problem?


Dim DBName As String
Dim DBRowNumFirst, DBRowNumLast, DBRows As Integer

DataRows = Cells(Rows.Count, 1).End(xlUp).Row
DBRowNumFirst = Application.Match(DBName, Range("A3:A" +
CStr(DataRows)), 0)
DBRowNumLast = Application.Match(DBName, Range("A3:A" + CStr(DataRows)),
1)