"FIND" generates "Type mismatch" error

[Guest]

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.

 

[Don Guillett]

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"

 

[Myrna Larson]

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 ....

 

[Paulw2k]

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

 

[Guest]

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

 

[Don Guillett]

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