VBA Macro - Mass Replacement Issue

ginkgoVil

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I have developed this code in VBA, but I am encountering a limitation of replacements for column C.

The code has worked perfectly most of the time, but I have encountered problems with cells in column C containing many characters and I only get replacement in part of the content. Any suggestions as to why this is happening?

VBA Code:
Public Sub Macro4()

Dim lastRow As Long
Dim thisRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
    Range("C:C").Replace What:=Cells(thisRow, "A"), Replacement:=Cells(thisRow, "B"), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next thisRow

End Sub

Thank you very much and best regards!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Are you trying to find a partial match, but then replace the entire contents?
 
Upvote 0
Sorry, I don't know how to edit the post or delete the reply. I think I typed the minisheet wrong.
 
Upvote 0
Can you re-post that, it looks as though you deleted come of the code.
 
Upvote 0
I attempt partial overlap and partial replacement in content.
I attach a screenshot. My goal is to look for Url1 matches in the Content Column and replace with Url2.
The replacement with my code is limited and I don't know why. The content cells can contain many Urls, but I can't replace them all.
Forgive me if I don't make myself clear. Thank you very much for your attention
 

Attachments

  • SampleSheet.png
    SampleSheet.png
    131.9 KB · Views: 15
Upvote 0
Can you post some examples of the urls that are not being changed, what you are searching for & what it should be replaced with.
 
Upvote 0
The objective is to find in column C all the cells that contain any url1 from column A and replace the matches with the url2 from column B.
A2 by B2
A3 by B3
A4 by B4
etc etc until the end.

I see that there is some kind of limitation on the replacements or maybe it is complicated to achieve this in 1200 rows.

I attach a screenshot of the content of a cell after running the macro. It finds 5 matches but misses some along the way.

Thanks
 

Attachments

  • SampleCelda.png
    SampleCelda.png
    222.3 KB · Views: 12
Upvote 0
How long is the text in that cell you have shown? It must be getting close to the limit, which may explain why some of the url were not replaced.
 
Upvote 0
The average is 20,000 characters per cell, more or less. Perhaps it is the problem and need should treat it differently. Thanks again
 
Upvote 0
The limit is about 32,700 characters per cell, so that could well be the reason.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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