VBA find and replace entire cell contents

[camspy]

Hello,

I am running Excel 2021 and I have a two sheets, here's Sheet1, it contains values of find (col A) and replace (col B), there might be 1k of rows in there:

Book1
	A	B
1	Abigail	<a href="https://example.com/abigail.html"><img src="https://example.com/abigail.jpg"> Abigail</a>
2	Sophia	<a href="https://example.com/sophia.html"><img src="https://example.com/sophia.jpg"> Sophia</a>
3	Amelia	<a href="https://example.com/amelia.html"><img src="https://example.com/amelia.jpg"> Amelia</a>
4	Elizabeth	<a href="https://example.com/elizabeth.html"><img src="https://example.com/elizabeth.jpg"> Elizabeth</a>
5	Ava	<a href="https://example.com/ava.html"><img src="https://example.com/ava.jpg"> Ava</a>
6	Isabella	<a href="https://example.com/isabella.html"><img src="https://example.com/isabella.jpg"> Isabella</a>
7	Gianna	<a href="https://example.com/gianna.html"><img src="https://example.com/gianna.jpg"> Gianna</a>
8	Harper	<a href="https://example.com/harper.html"><img src="https://example.com/harper.jpg"> Harper</a>
9	Mila	<a href="https://example.com/mila.html"><img src="https://example.com/mila.jpg"> Mila</a>
10	Olivia	<a href="https://example.com/olivia.html"><img src="https://example.com/olivia.jpg"> Olivia</a>
Sheet1

The cells in col B might get lengthy, like 1k characters long.

Sheet2 contains cells with contents that need get found and replaced using the data from Sheet1:

Book1
	A	B	C	D
1	Ava	Isabella	Olivia	Sophia
2	Isabella	Gianna	Abigail	Amelia
3	Gianna	Harper	Sophia	Elizabeth
4	Harper	Mila	Amelia	Mila
5	Mila	Olivia	Elizabeth	Olivia
6	Olivia	Abigail	Ava	Abigail
7	Abigail	Sophia	Isabella	Ava
8	Sophia	Amelia	Gianna	Isabella
9	Amelia	Elizabeth	Harper	Gianna
10	Elizabeth	Ava	Mila	Harper
Sheet2

There are like like 200 columns and 2k rows in Sheet2.

I was using some 10-year-old VBA code that stopped working in new Excel with long cell values and also it workes slow as heck.

Is it possible that some new VBA solution would work with long cell values (1k characters long) to use find and replace data from Sheet1 to change entire cell contents in Sheet2?
Any help is appreciated.
Thanks in advance.

 

[camspy]

Ok, I did that.

Here's the code I used for the test:

Sub MatchAndReplace()

Dim i As Long
Dim SearchString As String
Dim ReplaceString As String
Dim LookupArray As Range

Sheets(2).Copy After:=Sheets(2)
'Sheets(3).Name = "Result" 'Uncomment string this to set resulting sheet name
Set LookupArray = ThisWorkbook.Sheets(3).Cells(1, 1).CurrentRegion 'Will cycle through any number of pairs on Sheet1

For i = 2 To ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Rows.Count 'Does not use table header

    SearchString = ThisWorkbook.Sheets(1).Cells(i, 1).Text
    ReplaceString = ThisWorkbook.Sheets(1).Cells(i, 2).Text
'LookupArray.Replace What:=SearchString, Replacement:=ReplaceString, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

LookupArray = Replace(ThisWorkbook.Sheets(3).Cells(i, 1), SearchString, ReplaceString)

Next i

End Sub

It ran fine without errors.
The result - all empty cells

Can you try commenting out the original line and use this and let me know?

LookupArray = Replace(ThisWorkbook.Sheets(3).Cells(i, 1), SearchString, ReplaceString)

 

[Micron]

Was your data the same as what you told me to use in your prior post? I ran it again and got 4 columns by 10 rows of this
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><meta name="description" content="Effortless time conversion and world time. Schedule conference calls, webinars &amp; online meetings, plan travel and track flight arrival time across time zones." />

 

[camspy]

Yes, it was the same. I double checked, to make sure. Second try brought the same results - all cells got empty in Sheet3

Was your data the same as what you told me to use in your prior post? I ran it again and got 4 columns by 10 rows of this
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><meta name="description" content="Effortless time conversion and world time. Schedule conference calls, webinars &amp; online meetings, plan travel and track flight arrival time across time zones." />

 

[Micron]

Your code is virtually identical to mine (differences are of no consequence, such as removing commented lines). I put the code in a standard module. If you put it in a sheet module, can only suggest you move it. If that's not the issue, then there has to be something you're overlooking I guess. If you remain stuck after moving the code, making the workbook available is all I can think of, and I suspect that's not an option for you.

 

[camspy]

Well, I can't think of anything better than just hosting he file I was testing this all on.

Find.and.Replace.xlsm

drive.google.com

Could you download it and see how it runs?

Your code is virtually identical to mine (differences are of no consequence, such as removing commented lines). I put the code in a standard module. If you put it in a sheet module, can only suggest you move it. If that's not the issue, then there has to be something you're overlooking I guess. If you remain stuck after moving the code, making the workbook available is all I can think of, and I suspect that's not an option for you.

 

[Micron]

Your sheet1 posted sample contains 10 rows - so does sheet2. The data you put in your test file has 10 and 8. The mismatch causes the last 2 iterations of the loop to replace the range with nothing. Somehow I think that even if you fix that situation, the results will not be what you want. AFAIK, using any replace function on a range object with an address like $A$1:$D$10 is going to replace everything in the range with the same value. The final outcome will probably be whatever the last value was. Did you notice that in a prior post where I said the code worked and all the cells had the same value? Is that the outcome you want?

 

[camspy]

Let me show a simple example and the outcome that I'm expecting to get.

This is Sheet1

Find.and.Replace2.xlsm
	A	B
1	find	replace
2	Ava	1
3	Isabella	2
4	Gianna	3
5	Harper	4
6	Mila	5
7	Olivia	6
8	Abigail	7
9	Sophia	8
10	Amelia	9
11	Elizabeth	10
Sheet1

This is Sheet2

Find.and.Replace2.xlsm
	A	B	C	D
1	Gianna	Harper	Sophia	Elizabeth
2	Harper	Mila	Amelia	Mila
3	Mila	Olivia	Elizabeth	Olivia
4	Olivia	Abigail	Ava	Abigail
5	Abigail	Sophia	Isabella	Ava
6	Sophia	Amelia	Gianna	Isabella
7	Amelia	Elizabeth	Harper	Gianna
8	Elizabeth	Ava	Mila	Harper
Sheet2

This is the result, Sheet3

Find.and.Replace2.xlsm
	A	B	C	D
1	3	4	8	10
2	4	5	9	5
3	5	6	10	6
4	6	7	1	7
5	7	8	2	1
6	8	9	3	2
7	9	10	4	3
8	10	1	5	4
Sheet2 (2)

As you see, it works with simple data, but not, when I try to to place this in B2 on Sheet1 (it is 268 chars):

<meta data-fr-http-equiv="Content-Type" content="text/html; charset=utf-8"/><meta name="description" content="Effortless time conversion and world time. Schedule conference calls, webinars &amp; online meetings, plan travel and track flight arrival time across time zones." />

I get Runtime error 13.

If I shorten this string by deleting some chars from the end so it becomes 255 chars, like this, it works:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><meta name="description" content="Effortless time conversion and world time. Schedule conference calls, webinars &amp; online meetings, plan travel and track flight arrival time across ti

I then tried 255 char value consisting just of x:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

It works. But when I add one more, so it's 256 chars:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

It's Runtime error 13.

So, the problem is with the character length of the value.

How can I use 256 and longer values for the VBA to work?

Your sheet1 posted sample contains 10 rows - so does sheet2. The data you put in your test file has 10 and 8. The mismatch causes the last 2 iterations of the loop to replace the range with nothing. Somehow I think that even if you fix that situation, the results will not be what you want. AFAIK, using any replace function on a range object with an address like $A$1:$D$10 is going to replace everything in the range with the same value. The final outcome will probably be whatever the last value was. Did you notice that in a prior post where I said the code worked and all the cells had the same value? Is that the outcome you want?

 

[camspy]

@Fluff maybe you know how to bypass the 255 character limit in my VBA code?

 

[Micron]

Maybe review post 16 as I edited a couple of times and it seems we're going backwards. I showed how to avoid that error by replacing your sheet replace function with the vba replace function which doesn't have the 255 character limit. You still have the issue (perhaps edited above) that caused your sheet to go blank.

You lost me on these new desired results - there are no integers in your file or first post.

Sheet2 contains cells with contents that need get found and replaced using the data from Sheet1:

See if this gets you anywhere.

Sub MatchAndReplace()
''this code is ignoring row 1 of sheet(3). Why?
Dim i As Long
Dim SearchString As String, ReplaceString As String
Dim LookupArray As Range, rng As Range

Sheets(2).Copy After:=Sheets(2)
'Sheets(3).Name = "Result" 'Uncomment string this to set resulting sheet name
Set LookupArray = ThisWorkbook.Sheets(3).Cells(1, 1).CurrentRegion 'Will cycle through any number of pairs on Sheet1

For i = 2 To ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Rows.Count 'Does not use table header
    SearchString = ThisWorkbook.Sheets(1).Cells(i, 1).Text
    ReplaceString = ThisWorkbook.Sheets(1).Cells(i, 2).Text
    Set rng = Sheets(3).UsedRange.Find(SearchString)
    rng.Value = ReplaceString
Next i

End Sub

See my code comment at the beginning. That should avoid the sheet going blank, but the way you have it, your counter could be less than what you need. With what I posted, if the count is too many it won't matter. Why not change Sheets(1) to (3)?

 

[camspy]

Micron, this code works, but not the way I need.
Your code makes a replace of each found cell value once.

Now it needs some tweaking so all found values could get replaced.

Maybe review post 16 as I edited a couple of times and it seems we're going backwards. I showed how to avoid that error by replacing your sheet replace function with the vba replace function which doesn't have the 255 character limit. You still have the issue (perhaps edited above) that caused your sheet to go blank.

You lost me on these new desired results - there are no integers in your file or first post.

See if this gets you anywhere.

Sub MatchAndReplace()
''this code is ignoring row 1 of sheet(3). Why?
Dim i As Long
Dim SearchString As String, ReplaceString As String
Dim LookupArray As Range, rng As Range

Sheets(2).Copy After:=Sheets(2)
'Sheets(3).Name = "Result" 'Uncomment string this to set resulting sheet name
Set LookupArray = ThisWorkbook.Sheets(3).Cells(1, 1).CurrentRegion 'Will cycle through any number of pairs on Sheet1

For i = 2 To ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Rows.Count 'Does not use table header
    SearchString = ThisWorkbook.Sheets(1).Cells(i, 1).Text
    ReplaceString = ThisWorkbook.Sheets(1).Cells(i, 2).Text
    Set rng = Sheets(3).UsedRange.Find(SearchString)
    rng.Value = ReplaceString
Next i

End Sub

See my code comment at the beginning. That should avoid the sheet going blank, but the way you have it, your counter could be less than what you need. With what I posted, if the count is too many it won't matter. Why not change Sheets(1) to (3)?