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

--
Gary''s Student - gsnu200909

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.

Rick;570712 Wrote:
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!

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 column
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 of
copying that column C data back into column B using Copy with
PasteSpecial|Values. Now you can delete that helper column.
2. Use a macro. You posted this in the programming section so run this
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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157668

Microsoft Office Help

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

--
Gary''s Student - gsnu200909


"Rick" wrote:



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.

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.