CODEWITHSUNDEEP
excelcell
Eric mansen
Eric mansen

Reputation: 41

change the cell that contains your searchword

I'm currently facing this struggle, i will try to explain it like this. The content of the cells: uml434, ffjdfuml434, uml32323. I want to change these cells to uml only. but in reallity there are alot more of these cells, and also ones that you dont want to change. is there a easy way to do this?

Thanks alot

Upvotes: 0

Views: 28

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Here is a small example that you can adapt to your needs. In this demo:

1.the data is in column A.
2.the values are constants, not formulas

Sub umlChanger()
    Dim r As Range
    Dim s As String
    s = "uml"

    For Each r In Columns(1).SpecialCells(2)
        If InStr(r.Value, s) > 0 Then r.Value = s
    Next r
End Sub

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from Excel:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Upvotes: 2

Related Questions