This code deleted a cell from another sheet!

MarkLyons2

New Member
Joined
Oct 5, 2006
Messages
4
This code:

MsgBox ("Entering Sub SearchButton - Data!A2 is " & Range("Data!A2"))
Sheets("Settings").Select
Sheets("Settings").Activate
Sheets("Settings").Cells.Select
Selection.ClearContents
Sheets("Settings").Range("A1").Select
MsgBox ("Just before advanced filter - Data!A2 is " & Range("Data!A2"))

Will erase my search criteria on a *completely different* sheet.

Am I overlooking something obvious? Your help is very much appreciated. Wonderful forum you folks have here.
-Mark
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I don't know (not sure I understand the question) but you don't need all that selecting.

Code:
Sheets("Settings").Cells.ClearContents
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Not sure if I am followoing this correctly, but you are not referencing the search data correctly.

Unless you somehow have a range specifically named "Data!A2", using Range("Data!A2") is incorrect. VBA does not use the same cell references as worksheet formulas.

If you want to reference A2 on a sheet named Data:
Sheets("Data").Range("A2")
 

MarkLyons2

New Member
Joined
Oct 5, 2006
Messages
4
Just asking about deleting cells in other sheets.

msgbox (Range("MySheet!A2")) works just fine. Try it.
 

MarkLyons2

New Member
Joined
Oct 5, 2006
Messages
4

ADVERTISEMENT

I don't know (not sure I understand the question) but you don't need all that selecting.

Code:
Sheets("Settings").Cells.ClearContents

Originally I did not have it. I did that to *be sure* it could not reference the other sheet.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
msgbox (Range("MySheet!A2")) works just fine. Try it.
It would appear you are correct about that, sorry. I have never seen that coded that way before. I'm not going to start using it, though.

At any rate: There is nothing in the code you have *posted* that would delete anything on any other sheet. Is Data!A2 somehow linked to a cell on the Selection sheet? Say A2 contains the formula =Selection!$A$1. When you delete the contents on the Selection sheet, A2 gets wiped out as well since there is no value to display.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Re: Just asking about deleting cells in other sheets.

msgbox (Range("MySheet!A2")) works just fine. Try it.

I'm amazed :eek: But Von Pookie's syntax is the correct one to use.

Originally I did not have it. I did that to *be sure* it could not reference the other sheet.

The code I posted cannot possibly change anything on any other sheet.
 

MarkLyons2

New Member
Joined
Oct 5, 2006
Messages
4
msgbox (Range("MySheet!A2")) works just fine. Try it.
It would appear you are correct about that, sorry. I have never seen that coded that way before. I'm not going to start using it, though.

At any rate: Is Data!A2 somehow linked ...Say A2 contains the formula.... When you delete the contents on the Selection sheet, A2 gets wiped out as well since there is no value to display.

A2 is on a completely different sheet - "Data"
Is has an ordinary numeric value. It gets wiped out. I'm just going to make a global variable. Thanks anyway guy's. I can't expect you kind folks to explain microsoft - they work in mysterious ways.
 

Forum statistics

Threads
1,136,203
Messages
5,674,392
Members
419,506
Latest member
mpazr001

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