replacing text using a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I would like to run a macro that replaces all text that is not 'member' or
'centre' or 'customer support' with 'other'. Can someone please help? I could
do a search and replace, however there are about 20 other options that it
would be a long macro, and I imagine it would be better to do one that
includes 'if not', but not sure how to do it.
Thanks.
 
=IF(OR(A1="member",A1="centre",A1="customer support"),A1,"other")

If you want to replace the original data, copy the new data, select the old, Edit>Paste Special, check Values

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I would like to run a macro that replaces all text that is not 'member' or
| 'centre' or 'customer support' with 'other'. Can someone please help? I could
| do a search and replace, however there are about 20 other options that it
| would be a long macro, and I imagine it would be better to do one that
| includes 'if not', but not sure how to do it.
| Thanks.
 
Instead of using the 1st 2 lines which would work, how about this idea.
Modify to suit

Sub replace()
'Columns(1).replace "b", "other"
'Columns(1).replace "d", "other"
For Each c In Range("a2:a22")
Select Case c.Value
Case "a", "d", "f"
c.Value = "Other"
Case Else
End Select
Next c
End Sub
 
Hi,
thanks for your help. the only problem is that I am really only just
learning about macros and don't really understand your response. I would like
to select Column B and then do then replace the text. If possible, can you
please either explain your response or direct me to somewhere where I can
learn a bit more (and interpret your response)
Kind regards,
 
Back
Top