Custom function returns #NAME?

Question

I created the following function with this code to find the first numeric charactor in a string in a cell. Cell E3 contains the string to be checked. When I enter the formula =Pos_nonalpha(E3) it returns #NAME?. What am I doing wrong?

Thanks,

JCM

Function Pos_nonalpha(cell) As Long Dim i As Long For i = 1 To Len(cell) Dim Number Select Case Asc(Mid(cell, i, 1)) Case 0 To 64, 91 To 96, 123 To 191 Pos_nonalpha = i Exit Function End Select Next i Pos_nonalpha = 0End Function

Answer 1

Maybe you don't have macros enabled?

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

I changed "Cell" to "Cel" but still getting #NAME?. I tried entering in another cell, same result :(

Thanks,

JCM

Answer 3

Try not using "Cell" as your variable to pass as it's a reserved name in VBA

Function Pos_nonalpha(cel) As Long 

And amend any references to "Cell" in your UDF

Answer 4

Bill,

After I hit Alt & F11, when I click Module 1 in the project tree on the left, I see the code. When  I start to type the formula, Excel seems to recognize it as it appears in the function dropdown list.

Thanks,

JCM

Answer 5

Make sure the function is in a standard module, not a class module (such as ThisWorkbook or Sheet1)

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk