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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,572
Office Version
  1. 365
Platform
  1. Windows
Are you trying to find a partial match, but then replace the entire contents?
 

ginkgoVil

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Sorry, I don't know how to edit the post or delete the reply. I think I typed the minisheet wrong.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,572
Office Version
  1. 365
Platform
  1. Windows
Can you re-post that, it looks as though you deleted come of the code.
 

ginkgoVil

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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: 9

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,572
Office Version
  1. 365
Platform
  1. Windows
Can you post some examples of the urls that are not being changed, what you are searching for & what it should be replaced with.
 

ginkgoVil

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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: 8

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,572
Office Version
  1. 365
Platform
  1. Windows
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.
 

ginkgoVil

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
The average is 20,000 characters per cell, more or less. Perhaps it is the problem and need should treat it differently. Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,572
Office Version
  1. 365
Platform
  1. Windows
The limit is about 32,700 characters per cell, so that could well be the reason.
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

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
Top