Erase contents of a cell in a range and not lose the cell's formula

[mrlanier]

I have 2 ranges. Column A1:A1000 reads the values of column B1:B1000.
In other words, A1=B1, A2=B2 and so forth. For example, if I delete
the contents of A5 or enter a different number in A5, not only have I
deleted the value of B5 as reflected in A5, but I've also deleted the
formula A5=B5. What I want is for B5 to be the default value of A5,
unless I manually enter a different value in A5. I need this to be the
case with every row in the range. Does anyone have a suggestion?
Thanks.

Michael

 

[KC Rippstein]

I have something like this in a worksheet I use. I have an "Override"
column where I can see at a glance exactly which rows I have input a manual
override for.
In your example, I'd move column B way off to the right, say column AA.
In column AB, I'd put any manual overrides you want.
Then, to use your example, A1 =IF(ISBLANK(AB1),AA1,AB1) and copy that all
the way down.
Now whenever you need to see if the value in A is an override, you can tell
at a glance by looking at column AB. Obviously, you can move column AB
closer to the first available column (J, M, whatever) so you don't have to
scroll so far to the right.
- KC

 

[mrlanier]

Thanks KC. I'll give it a try. I have about 1500 rows, so if you
think of a macro, please let me know. I'm greatful.

Michael

 

[mrlanier]

What I really need is a macro similar to this:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If Not Intersect(TargetCell, Range("A1")) Is Nothing Then
If TargetCell = "" Then
TargetCell = "=Sheet1!B1"
End If
End If

The problem with this is it returns a single cell rather than a range.
It would be prohibitive to copy this macro the 1500 times I would need.
Any other suggestions?

Michael

 

[KC Rippstein]

Once you type the formula in A1, just use the fill handle to drag it down
all 1,500 rows.
With A1 highlighted, look for the small black box in the bottom right corner
of the cell. That is a fill handle. When you hover your mouse over it,
your cursor should change from a thick white cross to a thin black cross.
That's when you click the fill handle and just drag it down as far as you
need to go.

 

[KC Rippstein]

Well, it sounds like you probably know what a fill handle is, so I'm going
to assume my solution sans VBA is not going to work for you. I apologize
that I am not comfortable with VBA and tend to use it sparingly since most
of my documents are shared with other folks at the office. I don't want
people to have to mess with security and macros unless absolutely necessary,
so I use formulas to accomplish my needs as much as possible. Surely
someone can help you with your macro here or you can post your question to
the programming newsgroup.