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

 

[ChrisGT7]

Give a try to the following code and I hope it will help you:

Option Explicit

Sub Clean_ColumnC()
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant
    Dim I As Long, R As Long
    Dim Sht As Worksheet
    
    Set Sht = Worksheets("Clean")
    R = Sht.Range("C" & Rows.Count).End(xlUp).Row
    With Sht.Range("C1:C" & R)
        .Replace "  ", ""
        .Replace " .", "."
        .Replace "..", "."
    End With

    With Sht
        Arr1 = .Range("C1:C" & R)
        Arr2 = .Range("M1:M" & R)
        Arr3 = .Range("N1:N" & R)
    End With
    
    For I = 1 To R
        Arr1(I, 1) = Application.Clean(Arr(I, 1))
        If Arr1(I, 1) = Arr2(I, 1) Then Arr1(I, 1) = Arr3(I, 1)
    Next I
    
    Sht.Range("C1:C" & R).Value = Arr1
End Sub

 

[ChrisGT7]

Please ignore the above code and try this one:

Option Explicit

Sub Clean_ColumnC()
    Dim I As Long, R As Long, X As Long
    Dim Sht As Worksheet, Arr As Variant
   
    Set Sht = Worksheets("Clean")
    R = Sht.Range("C" & Rows.Count).End(xlUp).Row
   
    With Sht.Range("C1:C" & R)
        .Replace "  ", ""
        .Replace " .", "."
        .Replace "..", "."
    End With

    Arr = Sht.Range("C1:C" & R)
   
    For I = 1 To R
        Arr(I, 1) = Application.Clean(Arr(I, 1))
       
        X = Evaluate("IFERROR(MATCH(" & Range("C" & I).Address & ",M:M,0),0)")
        If X Then Arr(I, 1) = Evaluate("INDEX(N:N," & X & ")")
    Next I

    Sht.Range("C1:C" & R).Value = Arr
End Sub

 

[kvsrinivasamurthy]

Change the for loop as below

 For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Range("M:M").Find(Arr(i, 1))
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i

 

[topi1]

Change the for loop as below

 For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Range("M:M").Find(Arr(i, 1))
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i

Thank you. It gave me "type mismatch error". ChrisGT7's code worked. Thank you both.

 

[topi1]

Please ignore the above code and try this one:

Option Explicit

Sub Clean_ColumnC()
    Dim I As Long, R As Long, X As Long
    Dim Sht As Worksheet, Arr As Variant
  
    Set Sht = Worksheets("Clean")
    R = Sht.Range("C" & Rows.Count).End(xlUp).Row
  
    With Sht.Range("C1:C" & R)
        .Replace "  ", ""
        .Replace " .", "."
        .Replace "..", "."
    End With

    Arr = Sht.Range("C1:C" & R)
  
    For I = 1 To R
        Arr(I, 1) = Application.Clean(Arr(I, 1))
      
        X = Evaluate("IFERROR(MATCH(" & Range("C" & I).Address & ",M:M,0),0)")
        If X Then Arr(I, 1) = Evaluate("INDEX(N:N," & X & ")")
    Next I

    Sht.Range("C1:C" & R).Value = Arr
End Sub

@ChrisGT7 thank you for your prompt help. It worked great. Appreciate it!

 

[topi1]

Please ignore the above code and try this one:

Option Explicit

Sub Clean_ColumnC()
    Dim I As Long, R As Long, X As Long
    Dim Sht As Worksheet, Arr As Variant
  
    Set Sht = Worksheets("Clean")
    R = Sht.Range("C" & Rows.Count).End(xlUp).Row
  
    With Sht.Range("C1:C" & R)
        .Replace "  ", ""
        .Replace " .", "."
        .Replace "..", "."
    End With

    Arr = Sht.Range("C1:C" & R)
  
    For I = 1 To R
        Arr(I, 1) = Application.Clean(Arr(I, 1))
      
        X = Evaluate("IFERROR(MATCH(" & Range("C" & I).Address & ",M:M,0),0)")
        If X Then Arr(I, 1) = Evaluate("INDEX(N:N," & X & ")")
    Next I

    Sht.Range("C1:C" & R).Value = Arr
End Sub

@ChrisGT7 I am seeing one drawback in the code. Unlike line by line entry like in my code, in your code it does not seem to be doing a typical find and replace. The cell value in the C has to be exact match with the value in M. It can't be part of the string. In other words, melon in C and melon in M will replace it with whatever is N but not if C says melon is a fruit. TY.

​

 

[kvsrinivasamurthy]

Define Frng as range in the code

Dim Frng as range

 

[ChrisGT7]

@topi1, try to replace X with this line:

X = Evaluate("IFERROR(MATCH(""" & Arr(I,1) & """,M:M,0),0)")

 

[topi1]

@topi1, try to replace X with this line:

X = Evaluate("IFERROR(MATCH(""" & Arr(I,1) & """,M:M,0),0)")

I think I did what you suggested and got the following code. It did not work. Did I not do it correctly?

Option Explicit

Sub Clean_Replace_()
    Dim I As Long, R As Long, X As Long
    Dim Sht As Worksheet, Arr As Variant
  
    Set Sht = Worksheets("Replace")
    R = Sht.Range("C" & Rows.Count).End(xlUp).Row
  
    With Sht.Range("C1:C" & R)
        .Replace "  ", ""
        .Replace " .", "."
        .Replace "..", "."
    End With

    Arr = Sht.Range("C1:C" & R)
  
    For I = 1 To R
        Arr(I, 1) = Application.Clean(Arr(I, 1))
      
      
       X = Evaluate("IFERROR(MATCH(""" & Arr(I, 1) & """,M:M,0),0)")
        If X Then Arr(I, 1) = Evaluate("INDEX(N:N," & X & ")")
    Next I

    Sht.Range("C1:C" & R).Value = Arr
End Sub