Replace only in text and numbers (not in formulas)

[danovkos]

Hi, i tried search on google, also here in forum, but was not succsessfull.
I try to find and replace characters - text and numbers in my wb and replace it with other text and numbers. Problem is, that replace looks also in formulas and then it want to replace also in links in path, in formulas and so. But this i dont want.
Is any possibility to use replace funkcionality only in text, numbers but NOT IN FORMULAS?

thx a lot.

[mancubus]

hi.

perhaps...

Dim cll As Range
For Each cll In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 23)
    If cll.Hyperlinks.Count = 0 Then cll.Replace "OldVal", "NewVal"
Next cll

[danovkos]

Yes, this works greate. But only in actual sheet .
But i have a lot of sheets in my wb. How can i set, that macro do it for all my sheets in WB?
thx a lot for your help

[mancubus]

Dim ws As Worksheet, cll As Range
For Each ws In Worksheets
    For Each cll In ws.Cells.SpecialCells(xlCellTypeConstants, 23)
        If cll.Hyperlinks.Count = 0 Then cll.Replace "OldVal", "NewVal"
    Next cll
Next ws

[danovkos]

thx a lot, it works

[snb]

or

Sub tst()
    for each sh in sheets
       sh.Cells.SpecialCells(2).Replace "k", "P"
    next
End Sub

[mancubus]

@snb

that was my first attempt.
cells containing hyperlinks are also included in the constants.

[snb]

@mancubus

Yes you are right; I tested with formula hyperlinks.