Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the number for visible row | Excel Discussion (Misc queries) | |||
find the number for visible row | Excel Discussion (Misc queries) | |||
Find first visible row | Excel Programming | |||
How do I find and replace part of a hyperlink that isn't visible? | Excel Discussion (Misc queries) | |||
Help: Copying Visible Cells only to Visible cells! | Excel Programming |