Function sbLookupAddress(vLookupValue As Variant, _ rTableArray As Range, _ Optional ByVal lOccurrence As Long = 1, _ Optional lColumnOffset As Long, _ Optional lRowOffset As Long) As String 'Reverse("moc.LiborPlus.www") PB 26-Aug-2010 V0.10 'Looks up lOccurrence'th occurrence of vLookupValue in rTableArray and 'returns address of found cell offset by lRowOffset rows and lColumnOffset 'columns. If lOccurrence is negative the search is done bottom-up '(i.e. -1 finds the last value, -2 last but one, etc.).
Dim i As Long Dim rFound As Range, rLast As Range Dim iSearchDir As Integer
If lOccurrence >= 0 Then iSearchDir = xlNext Else iSearchDir = xlPrevious lOccurrence = -lOccurrence + 1 End If
With rTableArray If rTableArray.Cells(1, 1) = vLookupValue Then lOccurrence = lOccurrence - 1 If lOccurrence = 0 Then sbLookupAddress = .Cells(1, 1)(1, lColumnOffset + 1).Address(False, False) Exit Function Else Set rFound = .Cells(1, 1) Set rFound = rTableArray.Find(What:=vLookupValue, After:=rFound, _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, _ SearchDirection:=iSearchDir) Set rLast = rFound Do lOccurrence = lOccurrence - 1 If lOccurrence = 0 Then sbLookupAddress = rFound.Offset(lRowOffset, _ lColumnOffset).Address(False, False) Exit Function End If Set rFound = rTableArray.Find(What:=vLookupValue, After:=rFound, _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, _ SearchDirection:=iSearchDir) Loop While rLast.Address <> rFound.Address sbLookupAddress = CVErr(xlErrValue) End If End With
End Function
|