1

on Excel I'm trying to "Find and Replace" some text (the text is the same for every cell) and change it to multiple cells (600+ cells). The problem is that when I do it excel removes the formatting from the text.

I searched something and apparently you can do it via VBA, so I found this VBA Macro:

Sub CharactersReplace(Rng As Range, FindText As String, ReplaceText As String, Optional MatchCase As Boolean = False)
  'UpdatebyExtendoffice20160711
    Dim I As Long
    Dim xLenFind As Long
    Dim xLenRep As Long
    Dim K As Long
    Dim xValue As String
    Dim M As Long
    Dim xCell As Range
    xLenFind = Len(FindText)
    xLenRep = Len(ReplaceText)
    If Not MatchCase Then M = 1
    For Each xCell In Rng
        If VarType(xCell) = vbString Then
            xValue = xCell.Value
            K = 0
            For I = 1 To Len(xValue)
              If StrComp(Mid$(xValue, I, xLenFind), FindText, M) = 0 Then
                xCell.Characters(I + K, xLenFind).Insert ReplaceText
                K = K + xLenRep - xLenFind
              End If
            Next
        End If
    Next
End Sub

Sub Test_CharactersReplace()
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("Select a range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Call CharactersReplace(xRg, "<span style="background-color: #ffff00;">##</span>", "<span style="background-color: #ffff00;">asdasd</span>", True)
End Sub

(I posted a picture because I dont know how to paste the entire code with the CTRL+K command). The code seems ok but is red on the last line of Code (on the line where I have to decide which word it will be changed).

Any suggestion ?

Thank you very much

BigBen
  • 46,229
  • 7
  • 24
  • 40
Michelozzo8
  • 15
  • 1
  • 5
  • A [mcve] is required, see https://stackoverflow.com/editing-help if you're struggling. – jonrsharpe Mar 19 '19 at 16:13
  • Can you post the code? Someone will edit your code so it fits. – Damian Mar 19 '19 at 16:14
  • You have a red line on the procedure calling because there are a lot of `"` so it's taking more strings than it should. – Damian Mar 19 '19 at 16:27
  • You've got an issue with quotes. See [how do I put double quotes in a string in vba](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba) – BigBen Mar 19 '19 at 20:17
  • Hi guys, I figured it out with this line of code: Call CharactersReplace(xRg, "Test1", "Test2", True) Is working properly, but I have a question: instead of "Test2", I want to do ALT+ENTER on the Line of Excel. There is a code on VBA to do it? Thank you very much. – Michelozzo8 Mar 20 '19 at 08:58
  • Keep in mind that this code will not make any changes if a cell has more than 255 characters. – Ben Aug 06 '20 at 15:24

1 Answers1

0

The last line of code should be:

Call CharactersReplace(xRg, "<span style=""background-color: #ffff00;"">KK</span>"", ""<span style=""background-color: #ffff00;"">Kutools</span>", True)

Where every " inside the outermost quotes is doubled.

To answer the question you posted in the comments, Alt+Enter is equivalent to vbLf or Chr(10) in VBA.

Ben
  • 1,168
  • 13
  • 45