VB ClearContents problem

[LPittenger]

I am trying, when a certain cell changes, to delete the contents of a number of other cells.

Because the particular cells will vary, I am referring to them indirectly - named cells contain the Excel references to both the "trigger" cell and the "to erase" cells.


Thus the start of my VB macro is:

------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim clrRef As String

clrRef = Range("TriggerCell").Cells.Text

If Not Intersect(Target, Range(clrRef)) Is Nothing Then
:
:
------------------

This works. If picks up the reference to the triggering cell as a text string in the cell named "TriggerCell" and then finds thta cell itself through Range(clrRef).

I use the same trick to figure out what cells need to be cleared

------------------
:
:
Dim clrCells As String
clrCells = Range("ClearArea").Cells.Text

For Each c In Range(clrCells)

<?????????>
????????

Next

End If
---------------------

This also works, to pick up the proper range of cells whose refernce is recorded as a text string in the cell named "ClearArea". I've stepped through the debugger and seen that 'c' in the For loop properly takes each cell value in turn from the list provided in the "ClearArea"-named cell.



Here's the problem -- I CAN'T GET THE CELL VALUES TO CLEAR!


I've tried everything I can think of in place of ????????<?????????>
c.Value = ""
c.Value2 = ""
c.Cells.Value = ""
c.Cells(1,1).Value = ""
c.ClearContents
Range(c.Address).Value = ""

I tried no for loop, and just did:
Range(clrCells).ClearContents


Nothing works. Every cell, each correctly found in turn by 'c', remains unaffected.

What should I be doing?!



thanks!

 

[VoG]

What is this meant to do?

clrCells = Range("ClearArea").Cells.Text

because it certainly won't set a range.

I'm unclear what you want to do but did you try

 Range("ClearArea").ClearContents

 

[jindon]

So "c" is correctly looping through "Range(clrCells)", but doesn't clear the cells.

Is this what you are saying ?

Any error ?
Isn't the sheet protected?

 

[LPittenger]

What is this meant to do?

clrCells = Range("ClearArea").Cells.Text

because it certainly won't set a range.

No... it returns the text string stored in the cell named "ClearArea".

That's what I want -- that text string gives me the reference to the region(s) I *do* want to clear.

E.g. clrCells might be the text string "$A$20:$B$33,$F$22,$G$1:$G$9"

I'm unclear what you want to do but did you try

 Range("ClearArea").ClearContents

I *don't* want to clear the "ClearArea" range... that's what's giving me the clrCells string which tells me the area I do want to clear.


That's why I then loop (For each c in...) through Range(clrCells)... because that gives me, cell by cell, the cells indicated by that string. (E.g., in the example above, loops through $A$20, $B$20, $A$21, $B$21.... etc).

So "c" is correctly looping through "Range(clrCells)", but doesn't clear the cells. Is this what you are saying ?

Any error ?
Isn't the sheet protected?

Yes... c correctly loops through Range(clrCells) but I cannot find a working command to set the contents of each cell c to be empty.

c = "", c.Value="", c.ClearContents etc... none of these work. The values in each cell c remain unchanged.

No error is reported, and, for development purposes, I have unprotected the sheet and workbook and (just to be sure) also unlocked all the cells in Range(clrCells).


It's not just "" or ClearContents, by the way -- any value I try to set (e.g. c.Value="You stupid thing") also doesn't change the cell values.


Most of these cells I'm trying to change have a Data Validation list, but I removed that on some of them for testing/debugging purposes and their values still didn't change, so it doesn't look like that's a factor either.

 

[jindon]

How about try with the new workbook?

 

[LPittenger]

How about try with the new workbook?

Good call. I was so tunnel-visioned that never even occured to me.

Comparable code works fine in a new workbook. So, obviously, the problem is some workbook setting in my original one and not my algorhithm after all. Yet the original sheet has got the same unprotection and same no-access-restrictions settings as this new dummy workbook has.

What other settings might be inhibiting VBA from modifying cells in my original sheet? I'm a lot less familiar with VBA than with Excel...

 

[jindon]

Well, I don't understand the resaon why, but it also happen to me sometime, not so often though...