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

T

topi1

Active Member
Joined
Aug 6, 2014
Messages
252
Office Version
  1. 2010
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.

VBA Code: 
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
 
Sort by date Sort by votes

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I have corrected the code.
VBA Code: 
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 = Worksheets("Clean").Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False)
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
    
End Sub
 
Upvote 0
@topi1 I have unmarked post#3 as the solution as it obviously not one. In future if you realise that something is not working correctly please un-mark the solution. Thanks
 
Upvote 0
kvsrinivasamurthy said:
I have corrected the code.
VBA Code: 
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 = Worksheets("Clean").Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False)
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
   
End Sub
Click to expand...
Thank you so much. Will try this evening.
 
Upvote 0
@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

VBA Code: 
Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
    
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
    
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
    
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
CDLMN
1It is made by GM.GMGeneral Motors
2VWVolkswagen
3It is made by GM.
4It is made by GM.
5It is made by VW.
6It is made by GM.
Clean
 
Upvote 0
Solution
Alex Blakenburg said:
@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

VBA Code: 
Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
   
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
   
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
   
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
CDLMN
1It is made by GM.GMGeneral Motors
2VWVolkswagen
3It is made by GM.
4It is made by GM.
5It is made by VW.
6It is made by GM.
Clean
Click to expand...
Thank you. I can't wait to try your code. As to your comment, I agree entirely. I could have done better, and I am sorry. I guess it evolved from a word to a sentence or para in the column C. Sorry to have wasted people's valuable time. Hope to do better in the future.
 
Upvote 0
No worries. It happens quite a lot. You are ahead of the pack since you do seem to have XL2BB up and running.
Let me know if you are really after Option (a). I can't look at it until tonight my time though.
 
Upvote 0
Alex Blakenburg said:
@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

VBA Code: 
Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
  
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
  
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
  
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
CDLMN
1It is made by GM.GMGeneral Motors
2VWVolkswagen
3It is made by GM.
4It is made by GM.
5It is made by VW.
6It is made by GM.
Clean
Click to expand...
@ Alex Blankenburg
Alex Blakenburg said:
No worries. It happens quite a lot. You are ahead of the pack since you do seem to have XL2BB up and running.
Let me know if you are really after Option (a). I can't look at it until tonight my time though.
Click to expand...
@Alex Blakenburg As you guessed, I wanted option B. The code worked like a charm. I am going to stress it by adding more stuff in the M and N columns but so far it has worked flawlessly. Thank you so much. Saved me. I was going to create a sheet and use CONCATENATE formula to help me keep expanding the code by adding lines. I won't have to do that anymore.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
344
smozgur
smozgur
E
  • Question Question
Can you combine these 2 macros ?
Replies
6
Views
234
excel4us
E
B
  • Locked
  • Question Question
copy range of cells
Replies
1
Views
91
Joe4
Joe4
B
  • Solved
Combine two routines
2 3
Replies
27
Views
491
NoSparks
N
B
  • Solved
refering to a worksheet with a changing name
2
Replies
17
Views
524
bdt
B

Forum statistics

Threads
1,224,827
Messages
6,181,199
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top