PDA

View Full Version : Lookup Nth occurrence: cells formats



arimax926
03-10-2009, 03:47 AM
I'm using the following user-defined function to find the Nth occurrence of a number in a range (Table_array):

Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)

Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long

If Part_cell_match = False Then
xlLook = xlWhole
Else
xlLook = xlPart
End If

Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)

On Error Resume Next
lOcCheck = WorksheetFunction.CountIf _
(Table_array.Columns(Look_in_col), To_find)

If lOcCheck < Occurrence Then
Lookup_Occurence = vbNullString
Else
For lLoop = 1 To Occurrence
Set rFound = Table_array.Columns(Look_in_col).Find _
(What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _
MatchCase:=Case_sensitive)
Next lLoop

On Error GoTo 0

Lookup_Occurence = rFound.Offset(0, Offset_col)
End If
End Function


The function should return the content of a cell in the same row but in a different specified column.
Instead it doesn't work at all if the format of data in Table_array is "number". Furthermore, if I set the format to be "general" in Table_array, it works only for values with decimal part of the number equal to zero, that is, it finds "4,0" in Table_array, but not "4,5" (both values are present).
Can anybody help me to understand how to make it works for every kind of number format in Table_array?
I'm using Excel 2007 and XP sp3.
Thank you.
Bye

Bob Phillips
03-10-2009, 04:13 AM
Can you post a workbook with some data and an example of the call to that UDF?

arimax926
03-10-2009, 04:34 AM
3 examples: value to be found format is always "general", table_array values format changes.

mdmackillop
03-10-2009, 11:48 AM
Try
LookIn:=xlFormulas

mdmackillop
03-10-2009, 11:59 AM
A little simpler
Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
For i = 1 To Table_array.Cells.Count
If WorksheetFunction.CountIf(rFound.Resize(i), To_find) = Occurrence Then
Vlookup_Nth = rFound.Offset(i - 1, Offset_col)
Exit For
End If
Next

arimax926
03-11-2009, 09:54 AM
Your version of the routine works fine in every case.
Thank you!
Bye

mdmackillop
03-11-2009, 04:47 PM
A word of caution.
Looping is OK with a limited range. If you have to check thousands of cells, it can be a bit slow.