VBA: how to search for multiple text strings in a data field and if matched return one word

[techgrl]

Hi!

So I have been pulling my hair our trying to figure out how to build a VBA for a process that is so time consuming and repetitive and deadly.

background: I have hundreds of records of customer info and I am trying to categorize the job titles so that I can conduct some analysis with a pivot table. My problem is that there is about 20 different ways and variations of "manager" so Instead of autofiltering for "mrg." "Manager" "marketing manager" ect. and then replacing the text manually and using the enter and fill process (to create some standardization to compare "manager" to "director" to "c-level" to "consultant", etc.) and repeating this process over and over again...

I would like to create a VBA that would search the column "job title" for multiple text strings at once and if the text string was true in the cell then the cell would be replaced by a new text string "Manager". I was thinking a series of if functions within one vba but I am not sure if this is possible. I need help with developing the actually code and direction as if this is even possible.

ANY HELP would be so appreciated!!!!!!!!!!!!!!!!!!

 

[AlphaFrog]

This example code searches Sheet2 column C for various forms of "Manager" (change the RE.Pattern to suit) and replaces the cell contents with "Manager".

[COLOR=darkblue]Sub[/COLOR] Search_Col_C_For_Managers()
    
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] Long
    [COLOR=darkblue]Dim[/COLOR] RE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] RE = CreateObject("VBScript.RegExp")
    RE.IgnoreCase = [COLOR=darkblue]True[/COLOR]
    RE.Global = [COLOR=darkblue]True[/COLOR]
    RE.Pattern = [COLOR=#ff0000]"Manager|Mngr|Mgr|Mrg"[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Sheet2")
        [COLOR=darkblue]With[/COLOR] .Range("C2", .Range("C" & Rows.Count).End(xlUp))
            v = .Value
            [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](v) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v)
                [COLOR=darkblue]If[/COLOR] RE.Test(CStr(v(i, 1))) [COLOR=darkblue]Then[/COLOR]
                    v(i, 1) = "Manager"
                    j = j + 1
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] i
            .Value = v
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    MsgBox j & " 'Managers' found. ", , "Manager Search"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[anthonya2369]

already answered with better answer

 

[techgrl]

Thank you! I really wished it would work because that solution looks promising but I tried to enter this and run it and I received the message" visual basic editor: license information for this component not found. you do not have the functionality license in this design environment"

any suggestions? also If this were to work would I be able to create a vba that did this for several searches at once?

thanks again!

 

[AlphaFrog]

Thank you! I really wished it would work because that solution looks promising but I tried to enter this and run it and I received the message" visual basic editor: license information for this component not found. you do not have the functionality license in this design environment"

any suggestions? also If this were to work would I be able to create a vba that did this for several searches at once?

thanks again!

What version of both Windows and Excel do you have? If you have a Mac, this method may not work.

If this were to work would I be able to create a vba that did this for several searches at once?

It searches the entire column. Otherwise, elaborate.

 

[techgrl]

I am working with a mac and it is excel 2007. I know excel is best suited for a PC - I have one at home but this a a work project so I have to figure out on my macbook

Any suggestions?
thanks again!

 

[anthonya2369]

Does VBA work the same between MAC and Windows?

would this code work:

Sub SearchIDs()

Dim iCell       As Range
Dim strFind()   As String
Dim i           As Integer
Dim strReplace  As String
Dim strWords    As String

strWords = "Manager|Mgr|Mngr|Mrg|Manger"

strFind = Split(strWords, "|")

strReplace = "Manager"

For Each iCell In Selection
    For i = LBound(strFind) To UBound(strFind)
        If InStr(UCase(iCell.Value), UCase(strFind(i))) > 0 Then
            iCell.Value = strReplace
            GoTo NextOne
        End If
    Next i
NextOne:
Next iCell

End Sub

 

[AlphaFrog]

For future reference, if you have a question for Excel-Mac, include that version info. As you can see, I matters.

It looks like Anthony's code should work though I'm not a Mac expert.

You could also use the Advanced Filters feature.

 

[techgrl]

Hi I've uploaded a sample and more clear picture of the type of data and the task I am trying to do. I am hoping this will help, i added a comment box that explains my process too

https://docs.google.com/spreadsheets/d/1-v3u2RgThLVMnkLVWUbdIa_q5YVmg0bFBfE7sTUz0LI/edit?usp=sharing


Let me know if there is a light at the end of this dark excel tunnel I have been stuck in all day. (Grrrr. I wish i had the entire manual memorized.)
Thanks again