Changing values in formulas with VBA
 

Hi!

I would need some help with this case.

I am trying to replace "7;faux" for "8;faux" in an excel formula. I
have this code, but it only searchs in the cells, not the formulas.

Range("H258:H390").Select
Selection.Replace What:="7;faux", Replacement:="8;faux", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Could anyone let me know how to made it search in the formulas instead
of the cells?

Thanks alot!

Melanie


---
Message posted from http://www.ExcelForum.com/

Changing values in formulas with VBA
 

the crucial argument is missing

LookIn:=xlFormulas

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


mirca_00 wrote:

Hi!

I would need some help with this case.

I am trying to replace "7;faux" for "8;faux" in an excel formula. I
have this code, but it only searchs in the cells, not the formulas.

Range("H258:H390").Select
Selection.Replace What:="7;faux", Replacement:="8;faux", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Could anyone let me know how to made it search in the formulas instead
of the cells?

Thanks alot!

Melanie


---
Message posted from http://www.ExcelForum.com/

Changing values in formulas with VBA
 

Replace does not look in formulas. Use search to find cells with
formulas (LookIn:xlFormulas) and then use the Replace function on the
formula of the found objects.

mirca_00 < wrote:
Hi!

I would need some help with this case.

I am trying to replace "7;faux" for "8;faux" in an excel formula. I
have this code, but it only searchs in the cells, not the formulas.

Range("H258:H390").Select
Selection.Replace What:="7;faux", Replacement:="8;faux", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Could anyone let me know how to made it search in the formulas instead
of the cells?

Thanks alot!

Melanie


---
Message posted from http://www.ExcelForum.com/

Changing values in formulas with VBA
 

first there is no lookin argument for the replace command.

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase,
MatchByte)

second, replace can only look at formulas. How would it replace the
displayed value of a formula. It can't. Constants in a cell are also
considered to be formulas.

So if you code isn't working it must have something to do with the string
you are trying to replace rather than a missing argument to the
function/method.

--
Regards,
Tom Ogilvy

"mirca_00 " wrote in message
...
Hi!

I would need some help with this case.

I am trying to replace "7;faux" for "8;faux" in an excel formula. I
have this code, but it only searchs in the cells, not the formulas.

Range("H258:H390").Select
Selection.Replace What:="7;faux", Replacement:="8;faux", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Could anyone let me know how to made it search in the formulas instead
of the cells?

Thanks alot!

Melanie


---
Message posted from http://www.ExcelForum.com/