Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
Hi all,
Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
there is apparently something wrong with the formula that you are trying to
do the replace on when it errors. there is no other reason that I can think of that this should not work. -- Regards, Tom Ogilvy "Nicawette" wrote: Hi all, Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
ok tx for your help
I will retest it in a more accurate way Nic Tom Ogilvy a écrit : there is apparently something wrong with the formula that you are trying to do the replace on when it errors. there is no other reason that I can think of that this should not work. -- Regards, Tom Ogilvy "Nicawette" wrote: Hi all, Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
I've tested this procedure :
1- format a cell in text 2- copy the below formula in the cell =IF(26;TRUE;FALSE) 4- change the format of the cell into general 3- run this macro Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub and I've an error : "replace method of range class failed" ? BUT if I do a "Find and replace" it works, If I edit the cell and hit enter it works, the formula give the value "FALSE" is it an excel bug ? why this macros doesn't work, this maccro has been recorded with the "find and replace" menu, it has to be the same way of doing ???!!!! Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub Nicawette a écrit : ok tx for your help I will retest it in a more accurate way Nic Tom Ogilvy a écrit : there is apparently something wrong with the formula that you are trying to do the replace on when it errors. there is no other reason that I can think of that this should not work. -- Regards, Tom Ogilvy "Nicawette" wrote: Hi all, Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
Apparently you are not in the US. I believe the problem is the semicolon in
your formula - once VBA is involved (as it is in this case), it is expecting the formula to be in US english notation. So it doesn't see the formula as being valid. Test your formula with commas instead of semicolons to confirm. A workaround might be If you don't have any working formulas in the sheet, try cells.Numberformat = "general" cells.copy cells.PasteSpecial xlValues -- Regards, Tom Ogilvy "Nicawette" wrote: I've tested this procedure : 1- format a cell in text 2- copy the below formula in the cell =IF(26;TRUE;FALSE) 4- change the format of the cell into general 3- run this macro Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub and I've an error : "replace method of range class failed" ? BUT if I do a "Find and replace" it works, If I edit the cell and hit enter it works, the formula give the value "FALSE" is it an excel bug ? why this macros doesn't work, this maccro has been recorded with the "find and replace" menu, it has to be the same way of doing ???!!!! Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub Nicawette a écrit : ok tx for your help I will retest it in a more accurate way Nic Tom Ogilvy a écrit : there is apparently something wrong with the formula that you are trying to do the replace on when it errors. there is no other reason that I can think of that this should not work. -- Regards, Tom Ogilvy "Nicawette" wrote: Hi all, Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell.replace strange behaviour
Hi tom,
I'm using excel 2000 and you are right I'm not in the US. Unfortunately changing semicolons into commas gives me an error. Anyway I will try a workaround. Tx a lot for your kind help Tom Ogilvy wrote: Apparently you are not in the US. I believe the problem is the semicolon in your formula - once VBA is involved (as it is in this case), it is expecting the formula to be in US english notation. So it doesn't see the formula as being valid. Test your formula with commas instead of semicolons to confirm. A workaround might be If you don't have any working formulas in the sheet, try cells.Numberformat = "general" cells.copy cells.PasteSpecial xlValues -- Regards, Tom Ogilvy "Nicawette" wrote: I've tested this procedure : 1- format a cell in text 2- copy the below formula in the cell =IF(26;TRUE;FALSE) 4- change the format of the cell into general 3- run this macro Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub and I've an error : "replace method of range class failed" ? BUT if I do a "Find and replace" it works, If I edit the cell and hit enter it works, the formula give the value "FALSE" is it an excel bug ? why this macros doesn't work, this maccro has been recorded with the "find and replace" menu, it has to be the same way of doing ???!!!! Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub Nicawette a écrit : ok tx for your help I will retest it in a more accurate way Nic Tom Ogilvy a écrit : there is apparently something wrong with the formula that you are trying to do the replace on when it errors. there is no other reason that I can think of that this should not work. -- Regards, Tom Ogilvy "Nicawette" wrote: Hi all, Sub remplacement() Sheets("Sheet2").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="=", replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False End Sub I've recorded this macro to avoid hitting enter each time when I copy a formula located in a cell in text format into a new cell (not in text format). When I run this macro it works fine with a formula like "=x+5" or "=len()" but not with a formula like "=vlookup()" I have an error 1004 message saying that "replace method of range class failed". The amazing thing is that when I have recorded this macro, it worked fine with the =vlookup() and also worked fine when I've used the find and replace ???? Any idea to solve it ? Tx Nic. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace macro strange behaviour | Excel Discussion (Misc queries) | |||
Strange VBA Behaviour | Excel Programming | |||
Strange Cell Behaviour...? | Excel Programming | |||
Strange behaviour | Excel Worksheet Functions | |||
Strange behaviour in VBA Help | Excel Programming |