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]

Define Frng as range in the code

Dim Frng as range

@kvsrinivasamurthy The addition did the trick. However, the same issue as with ChrisGT7's code remains.

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.

 

[ChrisGT7]

@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.

 

[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.

Thank you.

 

[ChrisGT7]

@topi1, I tested my code and it worked just fine.

It replaced all the C values with the N values if C=M. The values that were not replaced were the ones that had a space at the end (e.x. melon <>melon).

Could you please check if there are any C values with spaces at their beginning or end?

 

[topi1]

@topi1, I tested my code and it worked just fine.

It replaced all the C values with the N values if C=M. The values that were not replaced were the ones that had a space at the end (e.x. melon <>melon).

Could you please check if there are any C values with spaces at their beginning or end?

Values in C are not constant. I cant imagine having space at the start or end of the word, sentence or paragraph in any of the cells in C. Hope that answers your question.

 

[kvsrinivasamurthy]

Try

For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False).Activate
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i

 

[topi1]

Try

For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False).Activate
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i

I got the following final code compiled from your directions. It gives errors. TY.

Sub Clean_ColumnC_K()
    Worksheets("Clean").Range("C1:C999").Replace "  ", ""
    Worksheets("Clean").Range("C1:C999").Replace " .", "."
    Worksheets("Clean").Range("C1:C999").Replace "..", "."
    
     Dim Frng As Range

    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))
        Set Frng = Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False).Activate
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
    
End Sub

 

[kvsrinivasamurthy]

Pl upload sample file having problem in some website and give link here. Explain where is the problem.

 

[topi1]

Pl upload sample file having problem in some website and give link here. Explain where is the problem.

Here is the link. Hope it is ok that I have transferred it to google sheet. You will have to remove the code and put it in the module in excel. Thank you for your continuing offer to help.

Clean

Clean GM,General Motors VW,Volkswagen It is made by GM. Exact code inserted in excel Sub Clean_ColumnC_K() Worksheets("Clean").Range("C1:C999").Replace " ", "" Worksheets("Clean").Range("C1:C999").Replace " .", "." Worksheets("Clean").Range("C1:C999").Replace "..", "." Dim Frng As Range Dim Arr,...

docs.google.com

 

[kvsrinivasamurthy]

Google sheets I don't know. Pl upload excel file.