Handy Feature Broke my Macro

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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:
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. :confused: Help please?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
Have you tried starting your 1st line with Worksheets ("yourworksheetname").Range(Range("A65000").....
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Thanks, I had not tried that. However it did not work. :oops:
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
You need to do it to all range objects.

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

HTH
Cal
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

I tried this:
Code:
ActiveSheet.Range(ActiveSheet.Range("A65000").End(xlUp).Offset(0, 6).Address, ActiveSheet.Range("G2")).Replace What:="", Replacement:="F", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
And it still didn't work. :cry: :LOL:
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I just ran this as a test, and it worked fine. Only affected the single worksheet.

Code:
Sub ReplaceWithF()
Dim Ws As Worksheet
Set Ws = ActiveSheet
Ws.Range(Ws.Range("G2"), Ws.Range("A65000").End(xlUp).Offset(0, 6).Address).Replace "", "F"
End Sub

HTH
Cal
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

Try it again but make sure you do a search across the entire workbook before you run it and then when you run it make sure you have "" value cells out side of the range. (Can be accomplished by entering ="" and then copy/pastespecialvalues)
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Oorang,
I finally out what you mean, you would think since I ran into the same thing before, that I would have picked it up quicker. :eek: Let me look into a few things and I will get back to you.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Oorang,
That SUCKS. It seems that Microsoft has add this feature and we have no access to it from VBA. This wouldn't be to bad, except Excel remembers the last setting that the find or replace was set to. The only way I have found to fix it is to manually change it to sheet. I checked the MSDN web site and I was not able to find any work around for it.
If anyone else has a better method, I would like to hear, since any macro using the find or replace could be affected by this issue.

Cal
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Very much so, fortunatly I had built in robust error handling and it caught it before the damge was done. I have been through every resource I can think of on this one and I just can figure out how to change it. I tried to interact directly with dialogbox only to discover that the dialog box xlDialogFormulaReplace actually displays the OLD dialog box.
Edit.5: Also using this old dialog box seems to disable the new dialog box completly until you close excel and reopen it.

Edit1:
I then proceeded to loop through ALL dialog boxes to see if I could find the new one in hoped it was there and undocumented but to no avail. If I cannot find a solution for this I will have to remove all replace functions from my code or put fences around them. This effectivly breaks quite a few things. There MUST be a way to programaticly set the "Within" back to "sheet".

Edit2: I found XlSearchWithin and xlWithinSheet in the Excel Library but I cannot figure out how to implement...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,633
Messages
5,832,780
Members
430,167
Latest member
Gogogoben

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top