Help with Selection.Replace

[Ayo]

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

 

[OssieMac]

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.

 

[OssieMac]

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.)

 

[Ayo]

No offence taken. Any help is appreciated.

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.)

 

[OssieMac]

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.

 

[Ayo]

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]

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.

 

[JLGWhiz]

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]

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.

 

[Ayo]

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]

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.

 

[JLGWhiz]

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]

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.