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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,119,273
Messages
5,577,144
Members
412,769
Latest member
VK12345
Top