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?

 

[Alex Blakenburg]

Are there any formulas in the example sheet ?
If not you could try this:

Sub TestReplace_v03()
    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 = ("")
   
    Dim arr As Variant
    Dim i As Long, j As Long
    arr = ws1.UsedRange.Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = Replace(arr(i, j), k, replacetext)
        Next j
    Next i
   
    ws1.UsedRange.Value = arr

End Sub

 

[Revco]

Are there any formulas in the example sheet ?
If not you could try this:

Sub TestReplace_v03()
    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 = ("")
  
    Dim arr As Variant
    Dim i As Long, j As Long
    arr = ws1.UsedRange.Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = Replace(arr(i, j), k, replacetext)
        Next j
    Next i
  
    ws1.UsedRange.Value = arr

End Sub

I think this works!!! Thank you for the help!!!