epression REPLACE

[Frank RoadRunner]

I need to replace a string in a range of cells. I use a syntax below. The
purpose is to replace a string in a formula by the new one and add a new item.

Formula in Excel Workbook:
=================
=Average(January!prumDS12;February!prumDS12;March!prumDS12;April!prumDS12;prumDS12) where expression "prumDS12" is a named range.

Sub ReplRefer()

DIM ORIG as Variant
DIM NEW as Variant

Rem This part of the code works properly
'================================================
'copying formula in a new sheet

Sheets(PrevSheet).[YearAver].Copy
Sheets(ActSheet).[YearAver].Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'setting values to variables

ORIG = ";PrumDS12"
NEW = ";" & PredList & "!PrumDS12"
'=================================================
Rem In this part the Macro goes through the instructions without announcing an
error but the formula remains unchanged in the end.

'Replacing the string in the variable ORIG by the string in the variable NEW
'[YearAver] is a range in a workbook

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

ORIG = ")"
NEW = ";PrumDS12)"

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Where is the mistake? Is there anybody who would be able to help me? Many
thanks for eventual answer. I have a similar case where the name of a month
beeing replaced. It works. In spite of the fact I did the code above
analogically it doesn't work properly.

Frank

 

[Peter T]

I don't follow how your formula could work with those semicolons instead of
commas. Are you sure it originally contains a ";PrumDS12". If not that's why
nothing changes.

I strongly suggest you rename the variable "NEW" to say sNEW. I'm surprised
it even works with the keyword New used as a variable. Although it may work
I'd change [YearAver] to Range("YearAver")

Regards,
Peter T

I need to replace a string in a range of cells. I use a syntax below. The
purpose is to replace a string in a formula by the new one and add a new item.

Formula in Excel Workbook:
==================

=Average(January!prumDS12;February!prumDS12;March!prumDS12;April!prumDS12;pr
umDS12) where expression "prumDS12" is a named range.

Sub ReplRefer()

DIM ORIG as Variant
DIM NEW as Variant

Rem This part of the code works properly
'================================================
'copying formula in a new sheet

Sheets(PrevSheet).[YearAver].Copy
Sheets(ActSheet).[YearAver].Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'setting values to variables

ORIG = ";PrumDS12"
NEW = ";" & PredList & "!PrumDS12"
'=================================================
Rem In this part the Macro goes through the instructions without announcing an
error but the formula remains unchanged in the end.

'Replacing the string in the variable ORIG by the string in the variable NEW
'[YearAver] is a range in a workbook

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

ORIG = ")"
NEW = ";PrumDS12)"

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Where is the mistake? Is there anybody who would be able to help me? Many
thanks for eventual answer. I have a similar case where the name of a month
beeing replaced. It works. In spite of the fact I did the code above
analogically it doesn't work properly.

Frank

 

[Frank RoadRunner]

Hi Peter,

thaks for your advice but the semicolons really work, I am Czech and use the
Czech version of MS Excel, where commas were replaced by semicolons in
formulas. The formula is correct and works in interactive regime in Excel.
More when I try to do it manually through Excel command, it works perfectly.
Your advice to use range("name") instead of [name] didn't help, the outcome
was the same as before. When I was going through the code step by step (F8)
everything looked like to be OK. Reading Help in VB for Application I learned
that I didn't make any mistake. That's why I tried to ask someone else fo
help. Maybe the problem could be in right declaring variables, but If I use
strings directly, the outcome is the same again. I have similar code where I
replace names of months and everything is OK. So I don't know where the
problem could be. It is the situation, I would say, everything works but
expected output is different or the same than you expected.

Once more Thanks.
Frank


Peter T píše:

I don't follow how your formula could work with those semicolons instead of
commas. Are you sure it originally contains a ";PrumDS12". If not that's why
nothing changes.

I strongly suggest you rename the variable "NEW" to say sNEW. I'm surprised
it even works with the keyword New used as a variable. Although it may work
I'd change [YearAver] to Range("YearAver")

Regards,
Peter T

I need to replace a string in a range of cells. I use a syntax below. The
purpose is to replace a string in a formula by the new one and add a new item.

Formula in Excel Workbook:
==================

=Average(January!prumDS12;February!prumDS12;March!prumDS12;April!prumDS12;pr
umDS12) where expression "prumDS12" is a named range.

Sub ReplRefer()

DIM ORIG as Variant
DIM NEW as Variant

Rem This part of the code works properly
'================================================
'copying formula in a new sheet

Sheets(PrevSheet).[YearAver].Copy
Sheets(ActSheet).[YearAver].Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'setting values to variables

ORIG = ";PrumDS12"
NEW = ";" & PredList & "!PrumDS12"
'=================================================
Rem In this part the Macro goes through the instructions without announcing an
error but the formula remains unchanged in the end.

'Replacing the string in the variable ORIG by the string in the variable NEW
'[YearAver] is a range in a workbook

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

ORIG = ")"
NEW = ";PrumDS12)"

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Where is the mistake? Is there anybody who would be able to help me? Many
thanks for eventual answer. I have a similar case where the name of a month
beeing replaced. It works. In spite of the fact I did the code above
analogically it doesn't work properly.

Frank

 

[Peter T]

Hi Frank,

I knew decimal separators vary between languages but I must admit I always
assumed the comma to separate range references would be consistent between
languages. Surprising, learn something every day!

You say it works fine manually. Record a macro, verify changes, press undo,
verify undo resets original, run the macro. Does it work or fail.

Regards,
Peter T

Hi Peter,

thaks for your advice but the semicolons really work, I am Czech and use the
Czech version of MS Excel, where commas were replaced by semicolons in
formulas. The formula is correct and works in interactive regime in Excel.
More when I try to do it manually through Excel command, it works perfectly.
Your advice to use range("name") instead of [name] didn't help, the outcome
was the same as before. When I was going through the code step by step (F8)
everything looked like to be OK. Reading Help in VB for Application I learned
that I didn't make any mistake. That's why I tried to ask someone else fo
help. Maybe the problem could be in right declaring variables, but If I use
strings directly, the outcome is the same again. I have similar code where I
replace names of months and everything is OK. So I don't know where the
problem could be. It is the situation, I would say, everything works but
expected output is different or the same than you expected.

Once more Thanks.
Frank


Peter T píse:

I don't follow how your formula could work with those semicolons instead of
commas. Are you sure it originally contains a ";PrumDS12". If not that's why
nothing changes.

I strongly suggest you rename the variable "NEW" to say sNEW. I'm surprised
it even works with the keyword New used as a variable. Although it may work
I'd change [YearAver] to Range("YearAver")

Regards,
Peter T

I need to replace a string in a range of cells. I use a syntax below. The
purpose is to replace a string in a formula by the new one and add a

new
item.

Formula in Excel Workbook:
==================

=Average(January!prumDS12;February!prumDS12;March!prumDS12;April!prumDS12;pr
umDS12) where expression "prumDS12" is a named range.

Sub ReplRefer()

DIM ORIG as Variant
DIM NEW as Variant

Rem This part of the code works properly
'================================================
'copying formula in a new sheet

Sheets(PrevSheet).[YearAver].Copy
Sheets(ActSheet).[YearAver].Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'setting values to variables

ORIG = ";PrumDS12"
NEW = ";" & PredList & "!PrumDS12"
'=================================================
Rem In this part the Macro goes through the instructions without announcing an
error but the formula remains unchanged in the end.

'Replacing the string in the variable ORIG by the string in the

variable
NEW

'[YearAver] is a range in a workbook

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

ORIG = ")"
NEW = ";PrumDS12)"

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Where is the mistake? Is there anybody who would be able to help me? Many
thanks for eventual answer. I have a similar case where the name of a month
beeing replaced. It works. In spite of the fact I did the code above
analogically it doesn't work properly.

Frank

 

[Frank RoadRunner]

Hi, it looks like I should put the problem on, whatever I do the result is
the same. I must repeat one mistake around. It is no problem to do the
replace in manual regime for the time being. There is an Czech proverb "The
morning is wiser to the night". Thank for your trying to help.

Frank

Peter T píše:

Hi Frank,

I knew decimal separators vary between languages but I must admit I always
assumed the comma to separate range references would be consistent between
languages. Surprising, learn something every day!

You say it works fine manually. Record a macro, verify changes, press undo,
verify undo resets original, run the macro. Does it work or fail.

Regards,
Peter T

Hi Peter,

thaks for your advice but the semicolons really work, I am Czech and use the
Czech version of MS Excel, where commas were replaced by semicolons in
formulas. The formula is correct and works in interactive regime in Excel.
More when I try to do it manually through Excel command, it works perfectly.
Your advice to use range("name") instead of [name] didn't help, the outcome
was the same as before. When I was going through the code step by step (F8)
everything looked like to be OK. Reading Help in VB for Application I learned
that I didn't make any mistake. That's why I tried to ask someone else fo
help. Maybe the problem could be in right declaring variables, but If I use
strings directly, the outcome is the same again. I have similar code where I
replace names of months and everything is OK. So I don't know where the
problem could be. It is the situation, I would say, everything works but
expected output is different or the same than you expected.

Once more Thanks.
Frank


Peter T píse:

I don't follow how your formula could work with those semicolons instead of
commas. Are you sure it originally contains a ";PrumDS12". If not that's why
nothing changes.

I strongly suggest you rename the variable "NEW" to say sNEW. I'm surprised
it even works with the keyword New used as a variable. Although it may work
I'd change [YearAver] to Range("YearAver")

Regards,
Peter T


message I need to replace a string in a range of cells. I use a syntax below. The
purpose is to replace a string in a formula by the new one and add a new
item.

Formula in Excel Workbook:
==================

=Average(January!prumDS12;February!prumDS12;March!prumDS12;April!prumDS12;pr
umDS12) where expression "prumDS12" is a named range.

Sub ReplRefer()

DIM ORIG as Variant
DIM NEW as Variant

Rem This part of the code works properly
'================================================
'copying formula in a new sheet

Sheets(PrevSheet).[YearAver].Copy
Sheets(ActSheet).[YearAver].Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'setting values to variables

ORIG = ";PrumDS12"
NEW = ";" & PredList & "!PrumDS12"
'=================================================
Rem In this part the Macro goes through the instructions without
announcing an
error but the formula remains unchanged in the end.

'Replacing the string in the variable ORIG by the string in the variable
NEW
'[YearAver] is a range in a workbook

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

ORIG = ")"
NEW = ";PrumDS12)"

[YearAver].Replace What:=ORIG, Replacement:=NEW, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

End Sub

Where is the mistake? Is there anybody who would be able to help me? Many
thanks for eventual answer. I have a similar case where the name of a
month
beeing replaced. It works. In spite of the fact I did the code above
analogically it doesn't work properly.

Frank