Prev: Pivottable
Next: Macro for Deleting rows with balnk cell
From: HunterX on 2 Jun 2010 10:39
I have been trying to come up with a formula that will read each cell in a
column and copy & paste only the cells that contain numbers with .50. I can
seem to properly configure the formula to read any whole number as long as it
contains .50 in it. I want these vnumbers pasted into a seperate column.

=IF(D21="*"+"0.50"),(D21),("")

Thank you very much for your assistance.



From: Gord Dibben on 2 Jun 2010 10:49
=IF(D21-INT(D21)=0.5,D21,"")

If you truly want the numbers copied and pasted you would have to use VBA.


Gord Dibben MS Excel MVP

On Wed, 2 Jun 2010 07:39:01 -0700, HunterX
<HunterX(a)discussions.microsoft.com> wrote:

>I have been trying to come up with a formula that will read each cell in a
>column and copy & paste only the cells that contain numbers with .50. I can
>seem to properly configure the formula to read any whole number as long as it
>contains .50 in it. I want these vnumbers pasted into a seperate column.
>
>=IF(D21="*"+"0.50"),(D21),("")
>
>Thank you very much for your assistance.
>
>

From: Gary''s Student on 2 Jun 2010 12:03
Install this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set A1 = Range("A1")
Set B1 = Range("B1")
Set C1 = Range("C1")
If Intersect(Target, A1) Is Nothing Then Exit Sub
If Not IsEmpty(B1) Then Exit Sub
If Not IsEmpty(C1) Then Exit Sub
Application.EnableEvents = False
A1.Clear
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
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 windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

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

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu201003


"HunterX" wrote:

> I have been trying to come up with a formula that will read each cell in a
> column and copy & paste only the cells that contain numbers with .50. I can
> seem to properly configure the formula to read any whole number as long as it
> contains .50 in it. I want these vnumbers pasted into a seperate column.
>
> =IF(D21="*"+"0.50"),(D21),("")
>
> Thank you very much for your assistance.
>
>
>
 | 
Pages: 1
Prev: Pivottable
Next: Macro for Deleting rows with balnk cell