"Flase" overwriting value in cell...want to preserve existing valu

R

Rick

Hi all:

I have a formula that checks column A for a "Yes" or "No".
If "Yes" I want to leave the value (whole number) in Column B intact (this
is where I'm having a problem).
If column A has a "No" I want to blank out the value in column B.

I place the following formula in cell B1 and drag down to apply to all of
the B column: =IF(A1="No"," ")

The problem is if column A has a "Yes" it does not retain the value in
column B, rather it overwrites it with "False".

How can I prevent this from happening?

Thanks!
 
G

Gary''s Student

Sub rick001()
For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
If r.Value = "No" Then
r.Offset(0, 1).Clear
End If
Next
End Sub
 
R

Rick

Thanks for the quick reply! Could you assist on how I can apply this to my
spreadsheet. Sorry, I'm a bit of a lightweight with Excel.
 
P

p45cal

Rick;570712 said:
Hi all:

I have a formula that checks column A for a "Yes" or "No".
If "Yes" I want to leave the value (whole number) in Column B intac
(this
is where I'm having a problem).
If column A has a "No" I want to blank out the value in column B.

I place the following formula in cell B1 and drag down to apply to al
of
the B column: =IF(A1="No"," ")

The problem is if column A has a "Yes" it does not retain the value in
column B, rather it overwrites it with "False".

How can I prevent this from happening?

Thanks!
Click to expand...

A cell can contain a formula OR a bare value. Not both.
2 solutions:
1.Add a 'helper' column to the right of column B and in the new colum
in cell C1 enter:
=if(A1="No","",B1)
and copy down as far as necessary.
You should now have a column that looks right. Now it's a case o
copying that column C data back into column B using Copy wit
PasteSpecial|Values. Now you can delete that helper column.
2. Use a macro. You posted this in the programming section so run thi
macro after selecting the cells you want processed in column B:
Sub blah()
For Each cll In Selection.Cells
If cll.Offset(, -1) = "No" Then cll.Value = Empty
Next cll
End Su
 
G

Gary''s Student

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
R

Rick

gsnu200909 and P45cal Thanks for the help!

I accomplished via the helper column. I wasn't aware that the formula was
conflicting with the hard code value.

I'm now going to take a bit of time to get acquainted with Macros.

Thanks again for the direction.
 
Top