Rename cells on based on content

FL390
FL390 used Ask the Experts™
on
Hi,

I am a bit new to VBA and I need a start.
I want a macro in excel that renames cells if a certain criteria is met, loops through all rows in column A and renames those cells.

So if I have the criterias
1. test
2. test2
3. test3

I want that cells containing test will be renamed to else
I want tjat test2 will be renamed to somethingelse
I want that test3 will be renamed to evensomethingelse.

and so on and so on so I can add some criterias and have those renamed as I want.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I have this code allthough I just want to add more criteria to it :)
And is that code optional?
[CODE]
Sub ReplaceAllData()

    Cells.Replace _
        What:="micro excel", Replacement:="macro excel", _
    LookAt:=xlWhole, MatchCase:=False

End Sub
[/CODE]
Top Expert 2015
Commented:
Use this code...
Saurabh...

Sub changedata()
    Dim rng As Range, cell As Range

    Set rng = Range("A1:A" & Cells(Cells.Rows.Count, "a").End(xlUp).Row)

    For Each cell In rng
        If InStr(1, cell.Value, "test2", vbTextCompare) > 0 Then
            cell.Value = Replace(cell.Value, "test2", "somethingelse")
        ElseIf InStr(1, cell.Value, "test3", vbTextCompare) > 0 Then
            cell.Value = Replace(cell.Value, "test3", "evensomethingelse")
        ElseIf InStr(1, cell.Value, "test", vbTextCompare) > 0 Then
            cell.Value = Replace(cell.Value, "test", "else")
        End If
    Next cell


End Sub

Open in new window

Hi,

Or..

Sub kTest()
Dim ReplaceWhat, ReplaceWith, i As Long

'both the array size should be same
ReplaceWhat = Array("test", "test1", "test2") 'add more here
ReplaceWith = Array("else", "somethingelse", "evensomethingelse")

With ActiveSheet.UsedRange
    For i = 0 To UBound(ReplaceWhat)
        .Replace ReplaceWhat(i), ReplaceWith(i), 1
    Next
End With
End Sub

Kris

Author

Commented:
Thanx!

Works great.
As I see the arrays are also working wioth wildcards like "*".
EirmanChief Operations Manager

Commented:
It appears that the author FL390 is accidently closing this question instead of accepting points

(Strange thing, I received an email saying that I had participated in this question when I was only monitoring it)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial