| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub or Function not defined message on finding Length
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" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub or Function not defined message on finding Length
Bud,
Fins isn't a VB function so you have to do this Lenth = WorksheetFunction.Find(" ", Cell2.Offset(0, -1).Value, 1) - 1 Mike "Bud" wrote: 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" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub or Function not defined message on finding Length
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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Bud" wrote: 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" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub or Function not defined message on finding Length
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 "Mike H" wrote: Bud, Fins isn't a VB function so you have to do this Lenth = WorksheetFunction.Find(" ", Cell2.Offset(0, -1).Value, 1) - 1 Mike "Bud" wrote: 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" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Message "user defined type not defined" | Excel Discussion (Misc queries) | |||
| Finding cells of different string length. | Excel Discussion (Misc queries) | |||
| Finding the length of proportional text strings | Excel Programming | |||
| Help finding the length of two different columns | Excel Programming | |||
| "User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
