标题: 请各位高手帮忙，搜索列问题，在线急求 [打印本页] 作者: kane880410 时间: 2009-6-30 08:54 标题: 请各位高手帮忙，搜索列问题，在线急求 我想请问各位高手如何搜索一列中连续几行的数字是否存在且定位。例：在A列中，1行的数字为2，2行的数字为4，3行的数字为3，4行的数字为6，5行的数字的3，6行的数字为7，7行的数字为3，8行的数字为2，9行的数字为3，10行的数字为1。加入我现在要搜索4，3，6这三个数字是否在这列中连续存在，如果存在，这三个数的位置在什么地方？请各位详解，谢谢各位高手，小弟在线等结果，谢谢大家。作者: 方漠 时间: 2009-7-1 09:29
'返回要查找的数据的Range Object. (摘自MrEXCEL 论坛)
'Wraping the VBA Find/FindNext methods into a function you can use in your code to return found range objects!
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim firstAddress As String
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False)
If Not c Is Nothing Then
Set Find_Range = c
firstAddress = c.Address
Do
Set Find_Range = Union(Find_Range, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Function
Sub Test() '用法示例
'Here 's just a few of the potential uses for this function...
'Select all cells in a range that contain 22 as part of the value:
Find_Range(22, Range("D10:G20")).Select
'Clear the range if the cell contains exactly 999, but if it's a formula leave it be:
Find_Range(999, Range("D10:G20"), xlFormulas, xlWhole).ClearContents
'Delete all rows that contain "X" in column A:
Find_Range("X", Columns("A"), MatchCase:=True).EntireRow.Delete
'Quickly scan the whole sheet if you like!
Find_Range(1000, Cells, xlFormulas, xlWhole).EntireRow.Select
'Copy all the rows that have the value 1000 in column D and paste to Sheet2: '整行COPY.
Find_Range(1000, Columns("D"), xlFormulas, xlWhole).EntireRow.Copy Range("Sheet2!A1")