Problem with find function in VBA

[André]

Hello,

I would like to use the function find in my code. The function work
correctly for all numbers but not for 1 value. The value exist, I
don't understand?

Function cherche(feuille As Object, txt As String, zone As String, p
As Boolean) As Long
' function which find in worksheet 'feuille' the value
' 'txt' in area 'zone'
' p = true => function return the row number
' p = false => function return the column number
' If value is not found, the function return 0
' -------------------------------------------------------
Dim z As String

feuille.Activate

z = Left(zone, InStr(1, zone, ":") - 1)
If feuille.Range(z) = txt Then
feuille.Range(z).Select
If p Then
cherche = feuille.Range(z).Row()
Else
cherche = feuille.Range(z).Column()
End If
Else
With feuille.Range(zone)
Set c = .Find(txt, LookIn:=xlValues, lookAt:=xlPart)
If Not c Is Nothing Then
If p Then
cherche = c.Row()
Else
cherche = c.Column()
End If
Else
cherche = 0
End If
End With
End If
End Function

 

[Tushar Mehta]

What is the number that is giving you problems?

More important...if you are using this as a user-defined function
(i.e., entering it in a worksheet cell) it should never work when used
on a worksheet other than the one containing the range to search.
Basically, a UDF cannot change the XL environment and you are doing
that with the Activate and Select methods.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

What is the value? Find often has problems with dates.

 

[André]

The project is the budget management of the enterprise
I give a sample

A B
-------------------------------------
1 | Production | Impro |
2 | 10000 | 11001 |
-------------------------------------
3 45 50
4 40 49


Well, the header is the cost center and the data is the number of
hours. The hours is the result of a SQL. For each cost center, i must
fill the good column with the good number.

I use the function with the cost center for the parameter for finding
the column number. If the cost center is not the last column of the
area, the function work correctly otherwise not.

for the sample above

cl = cherche (feuille, "10000", "A2:B2", false) in this case cl = 1
cl = cherche (feuille, "11001", "A2:B2", false) in this case cl = 0

but, if i use the function

cl = cherche (feuille, "11001", "A22", false) in this case cl = 2

I don't understand

Andre

 

[Tom Ogilvy]

cl = cherche (feuille, "11001", "A22", false)

is meaningless to me.