Excel VBA error when attempting to replace text that has multiple lines a cell

[Revco]

Below is some simple code I use to find and replace text. It works great when the text has 8 or less lines. When it's more than 8 lines it gives me the wonderful Mismatch Error 13. Basic example below -

Line 1 Line 2 Line Randomn Another Line So on and so forth

replacetext = ("")

k as string

k = sheets("exampleabove").cells(1, 1)

Set Sheet = Sheets("examplesheet")

Sheet.Cells.Replace what:=k, Replacement:=replacetext, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

This works perfectly if the cell has 8 or less lines, but after that i get the error.

Should i use some other variable than string?

 

[kevin9999]

The following code worked fine with 16 lines in the cell. Assumes your text-to-replace is "Line"

Option Explicit
Sub TestReplace()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("examplesheet")
    Set ws2 = Sheets("exampleabove")
    Dim k As String, replacetext As String
    k = ws2.Cells(1, 1)
    replacetext = ("")
    
    ws1.Cells.Replace k, replacetext, lookat:=xlPart
    
End Sub

From this:

Book1
	A	B
1	Line 1 Line 2 Line Random and so on and so forth Line 3 Line 4 Line 5 Line 6 Line 7 Line 8 Line 9
2
examplesheet

To this:

Book1
	A	B
1	1 2 Random and so on and so forth 3 4 5 6 7 8 9
2
examplesheet

 

[Revco]

The following code worked fine with 16 lines in the cell. Assumes your text-to-replace is "Line"

Option Explicit
Sub TestReplace()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("examplesheet")
    Set ws2 = Sheets("exampleabove")
    Dim k As String, replacetext As String
    k = ws2.Cells(1, 1)
    replacetext = ("")
   
    ws1.Cells.Replace k, replacetext, lookat:=xlPart
   
End Sub

From this:

Book1
	A	B
1	Line 1 Line 2 Line Random and so on and so forth Line 3 Line 4 Line 5 Line 6 Line 7 Line 8 Line 9
2
examplesheet

To this:

Book1
	A	B
1	1 2 Random and so on and so forth 3 4 5 6 7 8 9
2
examplesheet

Thank you for the reply. I tried with the code you provided and received the same error. The only difference is that instead of just replacing the "Line" I am replacing all of the text contained in cell(1, 1). However, I did some further analysis, and I am perplexed even more. Below is an edited version of what I am trying to replace. As a whole it fails. I thought that perhaps this means one portion of the text was throwing off VBA so I did each line individually and it worked fine. I did variations of multiple lines and it worked fine. I also attempted to make both cells as text format and still receive the same error. Any other ideas what may be the issue? (Side note: as you can see all I did was replace words with "mumbo" and "jumbo", but this simple change allowed me to successfully replace 9 lines of text as opposed to 8 - further confusing me.. lol)

Mumbo Jumbo Mumbo 0 (?) • Mumbo/Jumbo mumbo jumbo - Mumbo jumbo Key Activities • Mumbo INTs to Jumbo tenants • E2E Mumbo jumbo testing • Load mumbo to JUMBO • Prepare and load Mumbos Note: Mumbo-load mumbo jumbo via MUMBO for jumbo inputs, as needed • Mumbo Staging - Jumbo 1: Key mumbos via jumbos system Note: Staging to be mumbo against jumbo 1 mumbo jumbo for mumbo-dependent Jumbos

 

[Revco]

Thank you for the reply. I tried with the code you provided and received the same error. The only difference is that instead of just replacing the "Line" I am replacing all of the text contained in cell(1, 1). However, I did some further analysis, and I am perplexed even more. Below is an edited version of what I am trying to replace. As a whole it fails. I thought that perhaps this means one portion of the text was throwing off VBA so I did each line individually and it worked fine. I did variations of multiple lines and it worked fine. I also attempted to make both cells as text format and still receive the same error. Any other ideas what may be the issue? (Side note: as you can see all I did was replace words with "mumbo" and "jumbo", but this simple change allowed me to successfully replace 9 lines of text as opposed to 8 - further confusing me.. lol)

Mumbo Jumbo Mumbo 0 (?) • Mumbo/Jumbo mumbo jumbo - Mumbo jumbo Key Activities • Mumbo INTs to Jumbo tenants • E2E Mumbo jumbo testing • Load mumbo to JUMBO • Prepare and load Mumbos Note: Mumbo-load mumbo jumbo via MUMBO for jumbo inputs, as needed • Mumbo Staging - Jumbo 1: Key mumbos via jumbos system Note: Staging to be mumbo against jumbo 1 mumbo jumbo for mumbo-dependent Jumbos

One more thing. I did some more testing and this confuses me more. Below are two cells. The only difference is "Line 1" and "Line 12" (in red font). This minor change is the difference between success and failure.

 

[kevin9999]

Because the code in post #2 worked fine for me, the only way forward from my perspective is if you share your actual file via Google Drive, Dropbox or similar file sharing platform, so we're both looking at the same thing. Also explain what it is you're actually trying to replace, and with what (I didn't quite get that from your post #3).

 

[Revco]

Because the code in post #2 worked fine for me, the only way forward from my perspective is if you share your actual file via Google Drive, Dropbox or similar file sharing platform, so we're both looking at the same thing. Also explain what it is you're actually trying to replace, and with what (I didn't quite get that from your post #3).

Good morning. Thank you for the additional feedback. To simplify things I am isolating the issue in a separate workbook to eliminate any other potential conflicts with my project. Below are two sheets and the only difference between the cells is adding "• Line 1" (highlighted below in yellow.) I am replacing all of the text found the cells. Below is my code for each example.

Example 1 Code:
Option Explicit
Sub TestReplace()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("examplesheet")
Set ws2 = Sheets("exampleabove")
Dim k As String, replacetext As String
k = ws2.Cells(1, 2)
replacetext = ("")

ws1.Cells.Replace k, replacetext, lookat:=xlPart


End Sub

In example 1 the text is successfully removed without error.

Example 2 Code:
Option Explicit
Sub TestReplace()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("examplesheet")
Set ws2 = Sheets("exampleabove")
Dim k As String, replacetext As String
k = ws2.Cells(3, 2)
replacetext = ("")

ws1.Cells.Replace k, replacetext, lookat:=xlPart


End Sub

In example 2, the only difference is inserting "• Line 1" into the cell. When this change is made I receive the mismatch error.

Below is the text if you would like to try to copy and paste to test.

Mumo Jumbo Mumbo 0 (?) • Mumbo/Jumbo mumbo jumbo - Mumbo jumbo Key Activities • Mumbo INTs to Jumbo tenants • E2E Mumbo jumbo testing • Load mumbo to JUMBO • Line 0 Note: Mumbo-load mumbo jumbo via MUMBO for jumbo inputs, as needed • Line 2 Line 3

Mumo Jumbo Mumbo 0 (?) • Mumbo/Jumbo mumbo jumbo - Mumbo jumbo Key Activities • Mumbo INTs to Jumbo tenants • E2E Mumbo jumbo testing • Load mumbo to JUMBO • Line 0 • Line 1 Note: Mumbo-load mumbo jumbo via MUMBO for jumbo inputs, as needed • Line 2 Line 3

 

[kevin9999]

Please share your actual file via Google Drive, Dropbox or similar file sharing platform, so we're both looking at the same thing.

 

[Revco]

Unfortunately I am at work, and they have everything locked down from saving it to sharing platform. Best I can do at the moment is email it to you, but I assume you'd prefer not to do that. Let me know if there are any other options to get it to you.

 

[Revco]

Please share your actual file via Google Drive, Dropbox or similar file sharing platform, so we're both looking at the same thing.

Ok. I think I found a way around it. Here it is. Let me know if it doesn't work.

Google Drive: Sign-in drive.google.com

 

[kevin9999]

My fellow volunteer @Alex Blakenburg may have provided the answer to the problem - there's a limit of 255 characters for find & replace. Try doing "=Len(A1)" on the "examplesheet" and see what number is return. FYI when you share a file on Google Drive you need to share it with "anyone with the link".