因为程序中多次用到find方法,但是这个方法中的参数过于多,所以想一次性写好封装在一个函数内,同时把待检查的sheet作为另外一个参数,find方法返回一个range 对象,是否可以获得该对象的行和列;换成是数组作为参数,分别接受查找到的单元格的行或列,为什么还是不行?
函数代码如下
Function search(Target As String, searchItem As String, a())
With Sheets(Target).Cells
Set Match = .Find(What:=searchItem, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
If Not Match Is Nothing Then
' info found
a(0) = Match.Row
a(1) = Match.Column
search = a
Else
' nothing found
MsgBox "Nothing found !"
Exit Function
End If
End Function
调用时代码:
ReDim Location1(0 To 1) As Integer
ReDim Location2(0 To 1) As Integer
Location1 = search("PortDetails", Switch_Name_In_U & " IN FABRIC " & Switch_Name_In_U, Location1())
Location2 = search("PortDetails", "SWITCH COMPONENTS", Location2())
MsgBox "r1= " & Location(0)
MsgBox "r2= " & Location(0)会报错说类型不符,并且缺乏合理参数。
函数代码如下
Function search(Target As String, searchItem As String, a())
With Sheets(Target).Cells
Set Match = .Find(What:=searchItem, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
If Not Match Is Nothing Then
' info found
a(0) = Match.Row
a(1) = Match.Column
search = a
Else
' nothing found
MsgBox "Nothing found !"
Exit Function
End If
End Function
调用时代码:
ReDim Location1(0 To 1) As Integer
ReDim Location2(0 To 1) As Integer
Location1 = search("PortDetails", Switch_Name_In_U & " IN FABRIC " & Switch_Name_In_U, Location1())
Location2 = search("PortDetails", "SWITCH COMPONENTS", Location2())
MsgBox "r1= " & Location(0)
MsgBox "r2= " & Location(0)会报错说类型不符,并且缺乏合理参数。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货