Replace Multiple Cells

[collegeguy]

Hello everyone...here is my situation.

I'm trying to replace several words in a spreadsheet to match only one value. For example, when I attend an event and prospects enter that they are interested in either nursing, practical nursing, nurse or rpn, I would like those values to be changed to Nursing - Practical to match the data in my CRM before importing.

I've tried a few VBA codes but keep getting errors. I am using office 2007. Here is one that I've tried:

Sub ReplaceCellValues()
For Each Cell In Range("A1:A11")
If Cell = Nursing Then Cell.Value = Practical - Nursing
If Cell = rpn Then Cell.Value = Practical - Nursing
If Cell = Nurse Then Cell.Value = Practical - Nursing
If Cell = Practical Then Cell.Value = Practical - Nursing
Next
End Sub

I took computer programming in college 11 years ago and haven't used it since. Any help would be appreciated.

Thank you

 

[Maheshp]

Hi
i think u want to replace nursing,practical nursing,nurse or rpn as "Nursing - Practical" to enter the data in CRM
Try :

Sub test()
Results = Array("practical nursing", "nurse or rpn", "nursing")
For P = 0 To UBound(Results)
    Columns("A:A").Replace What:=Results(P), Replacement:="Nursing - Practical", LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False
      Next
End Sub

 

[collegeguy]

Thank you Maheshp, that did help but it only changed the word nursing.

Essentially what I have is a spread sheet with prospective students names, emails, phone number and then they enter what career field they are interested in. I capture this information using number on an iPad. Unfortunately, no software on the iPad allows you to use drop down lists so I'm trying to create a code or macro that will search through everyone's entry and match them to what I have in my CRM. Nursing is an example. Another is our business programs, some students type in accounting or accountant but in our CRM it's listed as Business - Accounting.

Up until now I've been going through the sheets manually and updating them but it would be nice to be able to run something that would go through and change everything automatically.