因为程序中多次用到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)会报错说类型不符,并且缺乏合理参数。