Sub or Function not defined message on finding Length

[Bud]

Hello

I am trying to get the Length of how many characters there are of the first
word in a task description.
However, I am getting a Sub or Function not defined message.
What is this about?
Lenth = Find(" ", Cell2.Offset(0, -1).Value, 1) - 1


The first word in the location of the cell offset(Task description) above
holds an abreviated change request number. First 3 characters are PRJ and
than the rest of it is the actual number less zero filling after the PRJ. It
will never be greater than 11 characters but the rule was to code it in the
first word as PRJ686 or PRJ1001. So I have to make this into PRJ00000686 and
PRJ00001001.

I have the whole construct of the code below to do this

Sheets("Fill-Down").Select
Cells.Select
Columns("e:e").Select
Dim Cell2 As Range

For Each Cell2 In Selection
If WorksheetFunction.IsNA(Cell2.Value) Then
'If Cell2.Value = "#N/A" Then
If Cell2.Offset(0, -1).Value = "General Admin - LA" Then
Cell2.Value = "0"
Cell2.Offset(0, 1).Value = "AD"
ElseIf Cell2.Offset(0, -1).Value = "Catalog - Help Desk Support" Then
Cell2.Value = "PRJ00000513"
Cell2.Offset(0, 1).Value = "HD"
ElseIf Left(Cell2.Offset(0, -1).Value, 3) = "PRJ" Then
Lenth = Find(" ", Cell2.Offset(0, -1).Value, 1) - 1
If Lenth = 6 Then
Cell2.Value = "PRJ00000" & Mid(Cell2.Offset(0, -1).Value,
4, 3)
Cell2.Offset(0, 1).Value = "EN"
ElseIf Lenth = 7 Then
Cell2.Value = "PRJ0000" & Mid(Cell2.Offset(0, -1).Value,
4, 4)
Cell2.Offset(0, 1).Value = "EN"
ElseIf Lenth = 8 Then
Cell2.Value = "PRJ000" & Mid(Cell2.Offset(0, -1).Value, 4,
5)
Cell2.Offset(0, 1).Value = "EN"
ElseIf Lenth = 9 Then
Cell2.Value = "PRJ00" & Mid(Cell2.Offset(0, -1).Value, 4, 6)
Cell2.Offset(0, 1).Value = "EN"
ElseIf Lenth = 10 Then
Cell2.Value = "PRJ0" & Mid(Cell2.Offset(0, -1).Value, 4, 7)
Cell2.Offset(0, 1).Value = "EN"
ElseIf Lenth = 11 Then
Cell2.Value = "PRJ" & Mid(Cell2.Offset(0, -1).Value, 4, 8)
Cell2.Offset(0, 1).Value = "EN"

 

[Mike H]

Bud,

Fins isn't a VB function so you have to do this

Lenth = WorksheetFunction.Find(" ", Cell2.Offset(0, -1).Value, 1) - 1

Mike

 

[Barb Reinhardt]

I think I'd use INSTR for this

Lenth = InStr(1, Cell2.Offset(0, -1).Value, " ") - 1

'Check for Lenth = -1 meaning you haven't found anything

 

[incre-d]

you could do the following

Public Function reCode(ByVal acode As Range) As String

scode = Split(acode.Value2, " ")(0) '<-- The first element using space
as spliiter
sPref = "PRJ"
sNum = Right(scode, Len(scode) - 3) '<--- Remove PRJ
sNewNum = WorksheetFunction.Rept("0", 8 - Len(sNum)) & sNum
remakecode = sPref & sNewNum


End Function