vba - In Excel, use "Range.Replace" method to substitute characters, but do not affect formulae -

i want create macro excel (2010) substitute characters of latin alphabet equivalents cyrillic alphabet.

at first glance, seems quite easy, e.g. this:

activesheet.range("a1:z500").replace "sht", chrw(1097) '--> щ activesheet.range("a1:z500").replace "sht", chrw(1095) '--> Щ 'and on relevant characters , character combinations 

however, when run on active worksheet, not cell values affected formulae. makes them worthless, because e.g. sum(b1:b3) become Сум(Б1:Б3) [i.e. using cyrillic letters] which, excel, gibberish.

therefore question: there way tell excel use replace method on cell values, not on formulae?

note: dirty workaround include procedure on each cell in range first checks if starts "=", , if so, leave cell contents unchanged. perhaps there better, less home-brewed way?

range.replace search in formulas.

there hasformula property in range object. using must iterate on cells in given range. if range quite big take quite long if trying replace in each of cells. trying replace if cell not empty, not numeric , not has formula.

sub test()   dim orange range   each orange in activesheet.range("a1:z500")   if not isempty(orange) , not isnumeric(orange.value) , not orange.hasformula     orange.replace what:="sht", replacement:=chrw(1097), matchcase:=true     orange.replace what:="sht", replacement:=chrw(1065), matchcase:=true   end if  next  end sub 

oh , must set matchcase if replace shall case sensitive.

second approach:

you use vba replace instead of range.replace showed in other answer. not range.formula range.value.

this faster.

sub test2()   dim orange range   each orange in activesheet.range("a1:z500")   if not isempty(orange) , not isnumeric(orange.value) , not orange.hasformula     orange.value = replace(orange.value, "sht", chrw(1097), , , vbbinarycompare)     orange.value = replace(orange.value, "sht", chrw(1065), , , vbbinarycompare)   end if  next  end sub