Remove prefix

[22strider]

Hello Friends

Could anyone please look at this part of the code that I am writing and let me know what am I doing wrong.

The code is supposed to copy prefix from the active cell and store it to the variable 'strPrefix'. The active cell values are in the format of 'xxx-yyyy' (of variable length ofcourse) and the code is supposed to look for dash "-" and get the string portion before the dash and store it to the variable.
So in my example the variable should get 'xxx' added to it.

strPrefix = Left(ActiveCell.Value, WorksheetFunction.Find("-", ActiveCell.Value, 1) - 1)

But when I run my code I am getting runtime error (debugging highlights this row) that "Unable to get the Find property of the WorksheetFunction class"

Thanks for your help
Rajesh

 

[Rick Rothstein]

And I am not able to find out what mistake did I make there. would it be possible to point out what am I doing wrong here? This will help me learn.

If you are talking about your first posted code line, I answered that in Message #3.

 

[22strider]

Hello Rick

Sorry for keep pressing for it. I for some reason can't find answer to my last question. I'll put my question and code here again.
Following is my complete code.

Sub RemovePrefix()

Dim strPrefix   As String

Dim Lr  As Long
Dim Lc  As Long
Dim i   As Long
Dim Prefix
Set Prefix = CreateObject("Scripting.Dictionary")

Prefix.Add "CZ", "a"
Prefix.Add "AD", "b"
Prefix.Add "PD", "c"
Prefix.Add "RN", "d"
Prefix.Add "GD", "e"
Prefix.Add "KD", "f"
Prefix.Add "KR", "g"
Prefix.Add "W", "h"
Prefix.Add "DVR", "i"
Prefix.Add "FP", "j"
Prefix.Add "TJ", "k"
Prefix.Add "TP", "l"
Prefix.Add "WP", "m"
Prefix.Add "BR", "n"
Prefix.Add "HS", "o"

Lc = ActiveCell.Column
Lr = Cells(ActiveSheet.Rows.Count, Lc).End(xlUp).Row

strPrefix = ""
For i = 0 To Lr Step 1

    
    
    
    strPrefix = Left(Cells(i, Lc).Value, InStr(Cells(i, Lc).Value, "-") - 1)
    
    If Prefix.Exists(strPrefix) Then
        ActiveCell.Value = Mid(Cells(i, Lc).Value, InStr(Cells(i, Lc), "-") + 1, Len(Cells(i, Lc).Value))
    End If
    
Next i

Prefix.RemoveAll

End Sub

I am getting run time error message "Application defined or Object defined error" with the following code line highlighted.

 strPrefix = Left(Cells(i, Lc).Value, InStr(Cells(i, Lc).Value, "-") - 1)

Thanks
Rajesh

 

[22strider]

Ok finally got it working. There were couple of mistakes I was making. The LEFT function was failing for entries that did not have any dash ("-") in them. Also I was updating active cell rather than the cell being referenced by the progressing row counter. Following is my final code:

Sub RemovePrefix()
' Removing selected prefixes after dash
Dim strPrefix   As String
Dim iDash   As Integer
Dim Lr  As Long
Dim Lc  As Long
Dim i   As Long
Dim Prefix ' Declaring dictionary object
Set Prefix = CreateObject("Scripting.Dictionary")

'Loading dictionary with the selected prefixes that are to be removed _
Adding prefixes as the keys rather than values so that I could use EXISTS _
method later on in the code

Prefix.Add "CZ", "a"
Prefix.Add "AD", "b"
Prefix.Add "PD", "c"
Prefix.Add "RN", "d"
Prefix.Add "GD", "e"
Prefix.Add "KD", "f"
Prefix.Add "KR", "g"
Prefix.Add "W", "h"
Prefix.Add "DVR", "i"
Prefix.Add "FP", "j"
Prefix.Add "TJ", "k"
Prefix.Add "TP", "l"
Prefix.Add "WP", "m"
Prefix.Add "BR", "n"
Prefix.Add "HS", "o"

Lc = ActiveCell.Column
Lr = Cells(ActiveSheet.Rows.Count, Lc).End(xlUp).Row 'Last row in the column of selected cell

strPrefix = ""
For i = 2 To Lr Step 1 'Looping through each cell in the range
    iDash = InStr(Cells(i, Lc).Value, "-")
    'If statement for bypasssing function LEFT if there was no dash ("-")
    If iDash > 0 Then
        strPrefix = Left(Cells(i, Lc).Value, iDash - 1)
            If Prefix.Exists(strPrefix) Then
                Cells(i, Lc).Value = Mid(Cells(i, Lc).Value, iDash + 1, Len(Cells(i, Lc).Value))
            End If
    End If
Next i

Prefix.RemoveAll 'Unloading the dictionary object

End Sub

Thanks everyone for your help on this. Your codes seem to be better options.
Rajesh

 

[Biz]

Rick,

Nice one-liner.

Biz