Can't replace text function in excel with VBA

user_18850081 0
Viewed 51

I'm new working with this so have some patience please.

I'm tried with search and replace but in cells with function doesn't allow me. I have an excel with too many functions, I need erase the function REDONDEAR from every cell with a specific color. When I run the Macros it doesn't change the cells.

Sub foo()
Dim MR As Excel.Range
Dim rngCell As Excel.Range
Dim rngCount As Long

Set MR = Sheets("Hoja1").Range("A1:A827")

rngCount = 1

For Each rngCell In MR
    If rngCell.DisplayFormat.Interior.Color = RGB(226, 239, 218) Then
        rngCell.Select
        ActiveCell.Replace What:="REDONDEAR", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        rngCount = rngCount + 1
    End If
Next rngCell
End Sub
vba excel
1 Answers

If this is a one-time task, than I'd use a built-in tool (Ctrl+H). But if not, well... As far as I can see, your code works fine. Three reasons why there is no result in your case:

  1. Wrong range.
  2. Wrong color.
  3. Wrong function name.

Here's what I'd try. Change the range from a column to UsedRange to erase something from every cell with a specific color. Remove DisplayFormat, if the color of a cell is exactly what you are looking for, not the color you can see on a screen, which may be modified, for example, by conditional formatting. Make sure, you are looking for the correct color and word.

Sub Foo()
Dim Data As Range
Dim Cell As Range 
Dim SheetOfInterest as String
Dim WhatToErase as String
Dim ColorMark as Long
    SheetOfInterest = "Hoja1"
    WhatToErase = "REDONDEAR"
    ColorMark = RGB(226, 239, 218)
    Set Data = Sheets(SheetOfInterest).UsedRange 
    For Each Cell In Data
        If Cell.DisplayFormat.Interior.Color = ColorMark Then
            Cell.Replace WhatToErase, Replacement:="", LookAt:=xlPart
        End If
    Next Cell
End Sub

I left DisplayFormat in case if it was an intentional choice in the first place. But if it wasn't, then I'd recommend to try this approach: use the parameter SearchFormat to find cells with a specific color.

sub Baz()
Dim Data As Range
Dim SheetOfInterest as String
Dim WhatToErase as String
Dim ColorMark as Long
    SheetOfInterest = "Hoja1"
    WhatToErase = "REDONDEAR"
    ColorMark = RGB(226, 239, 218)
    Set Data = Sheets(SheetOfInterest).UsedRange
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = ColorMark
    Data.Replace WhatToErase, Replacement:="", _
        LookAt:=xlPart, MatchCase:=False, SearchFormat:=True
    Application.FindFormat.Clear
End Sub
user_149096210
Related