Find only in visible cells....
 

Hi all,
I made a function which returns the row number containing a search criteria.
The function is passed the search string (id) and the range to search in
(usually a column). This works fine - but I tried to modify it to only search
in visible cells if an autofilter had been applied - but it manages to find
hidden cells etc... any takers?
TIA

Function FindRecord(id As String, col As Range) As Long
Dim i As Long
Set col = col.SpecialCells(xlVisible)
col.Select
i = 0
On Error Resume Next
i = Selection.Find(what:=id, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
FindRecord = i
End Function

Find only in visible cells....
 

Function FindRecord(id As String, Col as Range) As Long
Dim i As Long
i = 0
set rng = Col.Find(what:=id, _
After:=col(col.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then _
i = rng.row
FindRecord = i
End Function

Using xlValues should only find matches in visible cells.

--
Regards,
Tom Ogilvy


"Mike Iacovou" wrote in message
...
Hi all,
I made a function which returns the row number containing a search
criteria.
The function is passed the search string (id) and the range to search in
(usually a column). This works fine - but I tried to modify it to only
search
in visible cells if an autofilter had been applied - but it manages to
find
hidden cells etc... any takers?
TIA

Function FindRecord(id As String, col As Range) As Long
Dim i As Long
Set col = col.SpecialCells(xlVisible)
col.Select
i = 0
On Error Resume Next
i = Selection.Find(what:=id, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
FindRecord = i
End Function

Find only in visible cells....
 

thanks as ever Tom. I get a type mismatch on this line:
set rng = Col.Find(what:=id, _
After:=col(col.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
id is a string,col would have been columns("A:A") for example... TIA

Find only in visible cells....
 

If you had used Range("A:A"), it would have worked.

Anyway, this changed version should work:

Function FindRecord(id As String, Col As Range) As Long
Dim i As Long
i = 0
If Col.Count = 1 Then
Set Col = Col.Cells
End If
Set rng = Col.Find(what:=id, _
After:=Col(Col.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then _
i = rng.Row
FindRecord = i
End Function

--
Regards,
Tom Ogilvy

"Mike Iacovou" wrote in message
...
thanks as ever Tom. I get a type mismatch on this line:
set rng = Col.Find(what:=id, _
After:=col(col.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
id is a string,col would have been columns("A:A") for example... TIA