Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
When I run this click macro from inside a worksheet, nothing happens. The
changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Hi Ayo,
Firstly put a MsgBox in the sub immediately after the sub name to ensure it is being called. Next (and you should do this anyway) include all of the arguments/parameters for the Replace. Excel remembers the last used arguments/parameters for both Find and Replace irrespective of whether they are used in the interactive mode or in code. The 'remembered' arguments/parameters are then used as the defaults if they are not reset in the code. Easiest method of getting them correct is to record the Replace code. You cannot be sure what a user has been doing with Find and Replace prior to running the code so it is essential to reset the arguments/parameters. -- Regards, OssieMac "Ayo" wrote: When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Should have included this before.
Please don't take offence to my suggesting this but ensure you have the code in the correct worksheet module. (Right click on the worksheet tab name and select view code and ensure it is there.) -- Regards, OssieMac "Ayo" wrote: When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
No offence taken. Any help is appreciated.
"OssieMac" wrote: Should have included this before. Please don't take offence to my suggesting this but ensure you have the code in the correct worksheet module. (Right click on the worksheet tab name and select view code and ensure it is there.) -- Regards, OssieMac "Ayo" wrote: When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Yet another thing to check. I now also see you have a space in the middle of
'InSite Milestones' and in the replacement 'InSiteMilestones ' has no space in the middle but a space at the end. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
The sub is being called, I run it manually by stepping through the code so I
know it's being run. I just replace the earlier sub with this: Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End With Me.Range("A2").Select End If Me.Calculate End Sub Still doesn't work. "OssieMac" wrote: Hi Ayo, Firstly put a MsgBox in the sub immediately after the sub name to ensure it is being called. Next (and you should do this anyway) include all of the arguments/parameters for the Replace. Excel remembers the last used arguments/parameters for both Find and Replace irrespective of whether they are used in the interactive mode or in code. The 'remembered' arguments/parameters are then used as the defaults if they are not reset in the code. Easiest method of getting them correct is to record the Replace code. You cannot be sure what a user has been doing with Find and Replace prior to running the code so it is essential to reset the arguments/parameters. -- Regards, OssieMac "Ayo" wrote: When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Hello again Ayo,
You can discard my last post re the sheet name. That was due to the way the post split the lines and when I copied it into my VBA editor, that is what I ended up with. Anyway, the code runs for me. Obviously the Replace does not work for me because I have not got the formulas but the button caption changes and cells D7,D23,D39 all change as they should. Another test. (Again don't take offence) In Design mode, right click the button and select View Code and ensure the cursor goes to the code and does not create a new sub just in case you have a typo in the button name. If that does not work, I would try deleting the button and create a new one. -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
The button must be from the Control Toolbox to use the Click event. If the
button is from the Control Tool box, then put a break point on the first line of the IF statement, then click the button to see if it breaks on that line and highlights if for debugging. If it does not, then check the spelling of the button name to be sure it is exactly like that shown in the properties window. If the code works when you run it manually, then the problem has to be in either the type of button you are using or the name you are using in the title line of the code. "Ayo" wrote in message ... When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
I think I am not making myself clear. Let me try and see if I can explain
better. The code works fine when you click the button and when I step through it in debugg mode. My only problem is that these line of codes: With Selection .Replace What:="=$D$7", Replacement:="<""" End With the replacement is not made the =$D$7 is not replaced with <"". Eevrything else works the way they should except the replacement is not performed. "OssieMac" wrote: Hello again Ayo, You can discard my last post re the sheet name. That was due to the way the post split the lines and when I copied it into my VBA editor, that is what I ended up with. Anyway, the code runs for me. Obviously the Replace does not work for me because I have not got the formulas but the button caption changes and cells D7,D23,D39 all change as they should. Another test. (Again don't take offence) In Design mode, right click the button and select View Code and ensure the cursor goes to the code and does not create a new sub just in case you have a typo in the button name. If that does not work, I would try deleting the button and create a new one. -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
I think I am not making myself clear. Let me try and see if I can explain
better. The code works fine when you click the button and when I step through it in debugg mode. My only problem is that these line of codes: With Selection .Replace What:="=$D$7", Replacement:="<""" End With the replacement is not made the =$D$7 is not replaced with <"". Eevrything else works the way they should except the replacement is not performed. "JLGWhiz" wrote: The button must be from the Control Toolbox to use the Click event. If the button is from the Control Tool box, then put a break point on the first line of the IF statement, then click the button to see if it breaks on that line and highlights if for debugging. If it does not, then check the spelling of the button name to be sure it is exactly like that shown in the properties window. If the code works when you run it manually, then the problem has to be in either the type of button you are using or the name you are using in the title line of the code. "Ayo" wrote in message ... When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
OK Ayo. Now I know what to look for I can see the problem. I was off on the
wrong track altogether. I thought that you meant the code was not running at all instead of one line of code just not doing what it should. You need some more double quotes around the double quotes that are part of the string replacement. When the double quotes are part of the string, enclose them in more double quotes and still finish with double quotes at the end of the string. A bit hard to read in this post but if you copy the code and paste it to your VBA editor you should be able to see it better. ..Replace What:="=$D$7", _ Replacement:="<""""", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False I like to use a line break at the end of each argument like the above because I find it easier to read in a tabular format. -- Regards, OssieMac |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Looks like Ossie got the answer. You need quotes for the two double quotes
inside the regular double quotes, which means five double quotation marks at the end. "Ayo" wrote in message ... I think I am not making myself clear. Let me try and see if I can explain better. The code works fine when you click the button and when I step through it in debugg mode. My only problem is that these line of codes: With Selection .Replace What:="=$D$7", Replacement:="<""" End With the replacement is not made the =$D$7 is not replaced with <"". Eevrything else works the way they should except the replacement is not performed. "JLGWhiz" wrote: The button must be from the Control Toolbox to use the Click event. If the button is from the Control Tool box, then put a break point on the first line of the IF statement, then click the button to see if it breaks on that line and highlights if for debugging. If it does not, then check the spelling of the button name to be sure it is exactly like that shown in the properties window. If the code works when you run it manually, then the problem has to be in either the type of button you are using or the name you are using in the title line of the code. "Ayo" wrote in message ... When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<""" End With Me.Range("A2").Select ElseIf Me.cmdMarketRegion.Caption = "Region" Then Me.cmdMarketRegion.Caption = "Market" Me.Range("D7,D23,D39") = "Region" Me.Cells.Select With Selection .Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)", Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")" End With Me.Range("A2").Select End If Me.Calculate End Sub . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Selection.Replace
Thanks. I figured that out yesterday when I was playing around with it and
remmembered that I had the same problems once or twice before. "OssieMac" wrote: OK Ayo. Now I know what to look for I can see the problem. I was off on the wrong track altogether. I thought that you meant the code was not running at all instead of one line of code just not doing what it should. You need some more double quotes around the double quotes that are part of the string replacement. When the double quotes are part of the string, enclose them in more double quotes and still finish with double quotes at the end of the string. A bit hard to read in this post but if you copy the code and paste it to your VBA editor you should be able to see it better. .Replace What:="=$D$7", _ Replacement:="<""""", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False I like to use a line break at the end of each argument like the above because I find it easier to read in a tabular format. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection.Replace Problem | Excel Discussion (Misc queries) | |||
Selection.Replace What and double quotes | Excel Programming | |||
Selection.Replace size limitation | Excel Programming | |||
Excel: Add replace within selection functionality | Excel Discussion (Misc queries) | |||
Replace Values in range selection | Excel Programming |