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

Revco

New Member
Joined
Feb 21, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 
Are there any formulas in the example sheet ?
If not you could try this:

VBA Code:
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
 
Upvote 0
Solution

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Are there any formulas in the example sheet ?
If not you could try this:

VBA Code:
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!!!
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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
Back
Top