VBA / Excel - Replace value only if cell does not contain formula

Question

I'm attempting to use the replace function to change some values, however the way I'm currently doing it will change values in vital formula.

How can I have the replace function work only on cells with no formula, within one column? I tried If Not Columns("I").HasFormula Then but that prevents the replace from working on the entire column if a formula is found.

Columns("I").Replace What:="10", _
                            Replacement:="Five", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="9", _
                            Replacement:="Four", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="8", _
                            Replacement:="Three", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="7", _
                            Replacement:="Three", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="6", _
                            Replacement:="Two", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="5", _
                            Replacement:="Two", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="4", _
                            Replacement:="One", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="3", _
                            Replacement:="One", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="2", _
                            Replacement:="One", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("I").Replace What:="1", _
                            Replacement:="One", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

End If

BruceWayne · Accepted Answer · 2017-01-07 18:43:42Z

1

What about this:

Sub replaceFormulas()
Dim rng As Range
Set rng = Range("I:I")
With rng.SpecialCells(xlCellTypeConstants)
    .Replace What:="9", Replacement:="Four", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ' etc etc
End With
End Sub

If that works, next I suggest just using the workable range, as I doubt you have every cell in column I filled in. Perhaps get the last row and do Set rng = Range("I1:I" & lastRow) to save a little time.

Note: This worked if I had cells with 9 and ="9". It just replaced 9 and kept my ="9" there.

edited Jan 7, 2017 at 18:43

answered Jan 7, 2017 at 18:41

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

I might be tired, but I think the OP wants the exact opposite, not to modify cells with formulas

BruceWayne Over a year ago

@ShaiRado - D'oh! No, you're not too tired, I'm just not feeling well. I misread that, you're right. I've updated to switch to xlCellTypeConstants.

siggi_pop · Accepted Answer · 2017-01-08 00:40:01Z

Not the quickest macro but you could iterate over each cell like this:

Sub fixCol_I()

Dim cell As Range
Dim iMatch As Integer
Dim strWhat As Variant
Dim strReplc As Variant
strWhat = Array("10", "9", "8", "7", "6", "5", "4", "3", "2", "1")
strReplc = Array("Five", "Four", "Three", "Three", "Two", "Two", "One", "One", "One", "One")

For Each cell In Columns("I").rows
    If Not cell.HasFormula And cell <> "" Then
        For iMatch = 0 To UBound(strWhat)
            cell.Replace What:=strWhat(iMatch), _
                        Replacement:=strReplc(iMatch), _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False, _
                        SearchFormat:=False, _
                        ReplaceFormat:=False
        Next iMatch
    End If
Next cell

End Sub

Collectives™ on Stack Overflow

VBA / Excel - Replace value only if cell does not contain formula

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related