Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
epression REPLACE
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
epression REPLACE
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 "Frank RoadRunner" <Frank wrote in 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
epression REPLACE
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 "Frank RoadRunner" wrote in message ... 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 "Frank RoadRunner" <Frank wrote in 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
epression REPLACE
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 "Frank RoadRunner" wrote in message ... 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 "Frank RoadRunner" <Frank wrote in 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
Replace text with variable using VBA replace code? | Excel Programming | |||
How to Replace multiple words to replace using excell | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions |