Handy Feature Broke my Macro

[Oorang]

In Excel 2003 you have the option to to use the "find" feature (ctrl-F) over the entire worksheet. This is pretty cool and I'm glad they added it. However I have a macro that uses the following snippet of code:

Range(Range("A65000").End(xlUp).Offset(0, 6).Address, "G2").Replace What:="", Replacement:="F", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

The problem occurs when the last user has used the find replace feature to look in all sheets. In that circumstance, when the code executes, the macro does not seem to limit itself to replacing in just the specificed range (Range(Range("A65000").End(xlUp).Offset(0, 6).Address, "G2")) it will do it over the entire used range in the sheet.
I THINK if I could set the property back to just the activesheet right before the code executes, it will fix the problem. However I cannot seem to locate it in the help file or the support site. Help please?

 

[Andrew Poulsom]

I don't have Excel 2003, but have you tried recording a macro while changing the search within setting?

 

[Oorang]

Yah, that's actually always one of the first things I try when I hit a wall Unfortunatly the code generated for searching within sheets and searching within workbook is exactly the same. :x

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Macro1 Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 12/9/2005 by Oorang</SPAN>
<SPAN style="color:#007F00">'</SPAN>

<SPAN style="color:#007F00">'</SPAN>
    Cells.Replace What:="", Replacement:="F", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, ReplaceFormat:=False
    Cells.Replace What:="", Replacement:="F", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>, SearchFormat:=False, ReplaceFormat:=False
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Andrew Poulsom]

It seems it should be an argument for the Find and Replace methods, although I can't find it in any MSDN documentation. If you look at the Find method in the Object Browser (grey box at the bottom of the screen), what are the arguments?

 

[Oorang]

It says:

Function Replace(What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat]) As Boolean
Member of Excel.Range

Find says:
Function Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat]) As Range
Member of Excel.Range

None of which seem to refer to the sheet. Look in is for formula or values and look at is look at the whole value or look at part of the value (similar to instr)

 

[Andrew Poulsom]

It says:

Function Replace(What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat]) As Boolean
Member of Excel.Range

Looks like a bug/omission then. The constants are there, but not the argument.

 

[Oorang]

That's really frustrating.
I wonder how I go about reporting it to MS?
There has to be a work around though. I tried send keys, but the dialog box is modal. And I tried Dialogs(Xlreplaceformula) but it actually opens the legacy version...which might work, but I can't figure out a way to make it execute the search with the arguments I need without the user hitting replace all. To make a bigger mess of it, using th elegacy version breaks the current version.

Is the best second option to loop through the range and replace the values? I hate to do that it's so slow.

Edit: This is the best fix I was able to come up with for now. If anyone has any ideas, I am open.
<hr>
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Public</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Public</SPAN> Val1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Public</SPAN> Val2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Public</SPAN> Lookin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> Test()
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("F2", Range("F65536").End(xlUp))
Val1 = "2"
Val2 = "F"
<SPAN style="color:#00007F">Call</SPAN> Replacer(Rng, Val1, Val2, <SPAN style="color:#00007F">True</SPAN>)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> Replacer(Rng <SPAN style="color:#00007F">As</SPAN> Range, Val1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Val2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> Lookin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
<SPAN style="color:#00007F">Dim</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TempStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">If</SPAN> Lookin <> <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Y <SPAN style="color:#00007F">In</SPAN> Rng
        <SPAN style="color:#00007F">If</SPAN> Y.Value = Val1 <SPAN style="color:#00007F">Then</SPAN> Y.Value = Val2
    <SPAN style="color:#00007F">Next</SPAN> Y
    <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Y <SPAN style="color:#00007F">In</SPAN> Rng
        <SPAN style="color:#00007F">If</SPAN> InStr(Y.Value, Val1) <> 0 <SPAN style="color:#00007F">Then</SPAN>
        TempStr = Y.Value
        Mid(TempStr, InStr(TempStr, Val1), Len(Val1)) = Val2
        Y.Value = TempStr
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> Y
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>
<hr>