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.

 

[Micron]

If I understand what you're saying, you're doing 1,000 searches (sheet1 column A) over 400,000 cells (2000 rows * 200 columns) which is something like 400,000,000 searches. No wonder it takes a long time, especially if you're swapping between sheets each time you search (not that you'd see that taking place). Methinks you should post the code and see if anyone can make suggestions on how to tweak it. Otherwise, you might get suggestions to use arrays, which you might already be doing. You probably should clarify by example what's being replaced by what. I think you want to replace "Ava" in A1 with
"<a href=url here><img src=url here> Ava</a>" from Sheet1 B5 by first looking for Ava in that url. If your data really looks like that, why not just look for Sheet1 A5 (Ava) in Sheet2 A:A and if found, replace sheet2 A5 with B5 (the url)? You'd start at A1 of course, not A5.

Maybe you already know but please use code tags (vba button on posting toolbar) if you post your code.

 

[camspy]

Hi Micron.

I never had 2000 rows * 200 columns, it's either many rows but like 10 columns or many columns but not so many rows.

Here's the VBA that as I said I was using until now. And it doesn't work with cells with values consisting for 600-700 characters.

Sub MatchAndReplace()

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

'Creating copy of initial sheet for replase

Sheets(2).Copy After:=Sheets(2)
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

Next i

End Sub

Maybe it's possible to tweak it so it could work a little bit faster and accept long replacement values?

If I understand what you're saying, you're doing 1,000 searches (sheet1 column A) over 400,000 cells (2000 rows * 200 columns) which is something like 400,000,000 searches. No wonder it takes a long time, especially if you're swapping between sheets each time you search (not that you'd see that taking place). Methinks you should post the code and see if anyone can make suggestions on how to tweak it. Otherwise, you might get suggestions to use arrays, which you might already be doing. You probably should clarify by example what's being replaced by what. I think you want to replace "Ava" in A1 with
"<a href=url here><img src=url here> Ava</a>" from Sheet1 B5 by first looking for Ava in that url. If your data really looks like that, why not just look for Sheet1 A5 (Ava) in Sheet2 A:A and if found, replace sheet2 A5 with B5 (the url)? You'd start at A1 of course, not A5.

Maybe you already know but please use code tags (vba button on posting toolbar) if you post your code.

 

[Micron]

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

I guess I must have seen one too many zeros in that! vba arrays are not a strong point for me but that's what comes to mind as a faster method. However, the max size of the array is dependent on system resources, which might be fine for one user but not another. Otherwise I wonder if Find method of Range object would be any faster. Or instead of assigning 700 characters to the replace value, just use the cell address that you have already referenced - ThisWorkbook.Sheets(1).Cells(i, 2).Text

 

[camspy]

I didn't write that VBA. I don't really understand how to tweak it, to tell you the truth ?

I guess I must have seen one too many zeros in that! vba arrays are not a strong point for me but that's what comes to mind as a faster method. However, the max size of the array is dependent on system resources, which might be fine for one user but not another. Otherwise I wonder if Find method of Range object would be any faster. Or instead of assigning 700 characters to the replace value, just use the cell address that you have already referenced - ThisWorkbook.Sheets(1).Cells(i, 2).Text

 

[Micron]

to eliminate the 700 character variable you could try changing
Replacement:=ReplaceString,
to Replacement:= ThisWorkbook.Sheets(1).Cells(i, 2).Text

However, I don't think that's going to help much. Does this current code raise an error, and if so what is the number and it's message?

 

[camspy]

I've tried that, thanks, but it didn't help.

Both ways I'm getting this:

Run-time error '13';
Type mismatch

to eliminate the 700 character variable you could try changing
Replacement:=ReplaceString,
to Replacement:= ThisWorkbook.Sheets(1).Cells(i, 2).Text

However, I don't think that's going to help much. Does this current code raise an error, and if so what is the number and it's message?

 

[Micron]

I just opened a new wb, pasted in your posted data and ran your code as posted. It worked (well, it didn't fail and I have to assume the results are what you want). That error suggests you have some blank cells somewhere in the data. Tip: when you declare that there's an error always post what line causes it. Run it again and when it breaks into the code (assuming that's how your vb editor is working) enter these lines, complete with ? character, in the immediate window at the bottom:
?SearchString
?ReplaceString
Place cursor at the end of each line and hit enter. If a printed blank line appears under either one of those lines, there is no value in the cell and that's likely the problem. Try that and post your results.

 

[camspy]

Micron, thanks for helping me, however I didn't get how to do what you suggested.

Could you try one thing please?
In Sheet1 replace the contents of cells in col B with the following one:

<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." />

And then run the code.

I just opened a new wb, pasted in your posted data and ran your code as posted. It worked (well, it didn't fail and I have to assume the results are what you want). That error suggests you have some blank cells somewhere in the data. Tip: when you declare that there's an error always post what line causes it. Run it again and when it breaks into the code (assuming that's how your vb editor is working) enter these lines, complete with ? character, in the immediate window at the bottom:
?SearchString
?ReplaceString
Place cursor at the end of each line and hit enter. If a printed blank line appears under either one of those lines, there is no value in the cell and that's likely the problem. Try that and post your results.

 

[Micron]

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)