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
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