PDA

View Full Version : [SOLVED] Replace only in text and numbers (not in formulas)



danovkos
08-24-2014, 11:39 PM
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
08-25-2014, 12:24 AM
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
08-25-2014, 12:32 AM
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
08-25-2014, 12:51 AM
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
08-25-2014, 01:11 AM
thx a lot, it works
:)

snb
08-25-2014, 01:26 AM
or

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

mancubus
08-25-2014, 02:34 AM
@snb

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

snb
08-25-2014, 03:14 AM
@mancubus

Yes you are right; I tested with formula hyperlinks.