I have the following code, but it generates a type mismatch error. Also, I
need to allow for the event nothing is found. Can someone please show me how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.

1st be aware that you are searching in col 23 of the used range which
may/may not be col 23 of the sheet. Suggest just columns(23) if that is the
column you want. Add an error trapper as shown.

Dim lngRow as Long
on error goto notfound

lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

exit sub
notfound:
msgbox "not found"



--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
I have the following code, but it generates a type mismatch error. Also, I
need to allow for the event nothing is found. Can someone please show me
how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.

You've probably alluded to the problem -- if the target isn't found, the
result of Find is Nothing, and Nothing doesn't have a row number. Try
splitting it up:

Dim lngRow as Long
Dim FoundCell As Range

Set FoundCell = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
'??
Else
lngRow = FoundCell.Row
End If

or leave as-is and use error trapping:

Dim lngRow as Long
On Error Resume Next
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
If Err.Number < 0 Then ....

On Tue, 16 Nov 2004 05:14:04 -0800, quartz
wrote:

I have the following code, but it generates a type mismatch error. Also, I
need to allow for the event nothing is found. Can someone please show me how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.

Hi Quartz,

You need a bit of error trapping code in your routine to accommodate this
eventuality.

If there is an error, immediately after the find-code add an if-line:

If lngRow = 0 then
OR
If Err then

And deal with the error. In my example I have just added a message box and
exit the routine.




Sub FindMe()
Dim Rng as Range
Dim lngRow as Long

On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns(23)
With Rng
lngRow = .Find(What:="A", After:=.Cells(1), LookIn:=xlFormulas,
LookAt:=xlWhole, _
SearchOrder:=xlByRows,SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Row
End With
If lngRow = 0 Then
MsgBox "Nothing found!"
Exit Sub
Else
Rem Otherwise put whatever you want to do here.
End If
On Error GoTo 0

End Sub

Regards
Paul

"quartz" wrote in message
...
I have the following code, but it generates a type mismatch error. Also, I
need to allow for the event nothing is found. Can someone please show me
how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.

Thanks very much to all who replied. I got it working.

"quartz" wrote:

I have the following code, but it generates a type mismatch error. Also, I
need to allow for the event nothing is found. Can someone please show me how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.

Always nice to show your solution for those reading the archives.

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks very much to all who replied. I got it working.

"quartz" wrote:

I have the following code, but it generates a type mismatch error. Also,
I
need to allow for the event nothing is found. Can someone please show me
how
to correct this?

Dim lngRow as Long
lngRow = ActiveSheet.UsedRange.Columns(23).Find(What:="A", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

Thanks much in advance.