CReplace value in the column if it matches with another column.

[topi1]

I have a following code that helps me clean cells in the column C for double spacing etc. I can add more rows in the vba to add more replacements, eg replace "bball" with "basketball". However, it is not efficient if I want to replace more than few strings. I want to have a code where if a cell value in the column C matches with a cell value in the column M, say cell M2, then the value in the cell in the column C is replaced with the value in N2. I can use vlookup or Index and MATCH formula for C lookup in the M:N range. However, I was wondering if there is vba that can do that. Thank you.

Sub Clean_ColumnC()
    Worksheets("Clean").Range("C1:C999").Replace "  ", ""
    Worksheets("Clean").Range("C1:C999").Replace " .", "."
    Worksheets("Clean").Range("C1:C999").Replace "..", "."

    Dim Arr, i As Long
    Arr = Worksheets("Clean").Range("C1:C999")
    For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
    Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
    
End Sub

 

[topi1]

@topi1, you did it correctly alright.

It is strange that it doesn't work because the code uses INDEX and MATCH, so it does an exact match (not case-sensitive though).
For example: Melon=melon=MELON but melon<>meLon1<>melon is a fruit

I'll try to make a test and keep you informed.

@ChrisGT7 I am sorry I wasted your time. I should have been cleared in my initial post. Thank you.

 

[Alex Blakenburg]

Love that you acknowledged everyone who pitched in . Glad we could help.