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

  • Thread starter Thread starter Rick
  • Start date Start date
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!
 
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
 
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.
 
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
 
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.
 
Back
Top