Mac Excel 2011 VBA UDF not working - SET or FIND c

问题:

I am converting a spreadsheet from Excel 2007 to Mac Excel 2011. I have tried for hours to solve my problem, without success, so any help would be appreciated!

This UDF finds a string in a range, then returns the value one cell below the found cell. The SET command returns nothing in the Mac version, but works in Excel 2007.

Function FindRng(Fnd As String)
Application.Volatile

Dim Rng As Range
If Fnd = "" Then
    FindRng = 0
    GoTo 109
End If
With Sheets("Matrix").Range("G2:FZ13")
         Set Rng = .Find(What:=Fnd, _
                After:=.Cells(2, 7), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
    If Not Rng Is Nothing Then
        FindRng = Sheets("Matrix").Cells(Rng.Row + 1, Rng.Column).Value
    Else
        FindRng = 0
    End If
End With

109 End Function

回答1:

Find doesn't work in a UDF called from a cell in 2011 (same problem that existed in PC versions pre-Office XP) so you'll either have to loop and test each cell (Loading the data into an array should be faster than reading cell-by-cell) or process one row at a time using application.match for example.