Syntax for running a macro from an =IF function?

[BobH]

Hi,

I want to run a macro that simply moves data from one row
to an area of the worksheet that keeps track of completed
items. What I need is a statement that does something
like this:

=IF(b1="",do nothing, run move macro).

How do I tell the =IF function to run the macro? This
seems to be a pretty easy thing to go yet the Help does
not address it.

Thanks,

Bob

 

[Chip Pearson]

You can't really call Sub type procedures from within worksheet
formulas. A function called from a worksheet formula can only
return a result to the calling cell. You might be able to use the
Change or Calculate event procedure to do what you want. Perhaps
you could provide a few more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[BobH]

What I have is a place in a spreadsheet for people to put
in action items and a complete date (I know, there are
much better tools for this, but this is the tool they want
to use). If there is a completion date (i.e. it isn't
blank) then I want to move that entry down to a separate
section containing completed actions.

My thought was to write a macro that checked each cell for
a completed date and if it contained an entry to simply
cut and paste it onto the end of the list at the bottom.

Thanks for your help.

Bob

 

[Otto Moehrbach]

Bob
Here is an event macro that reacts when an entry is made in column D.
The macro copies Columns A of that row and pastes it to the bottom of the
data range in Column A. It then deletes the row of the original entry.
You will probably have to make some changes to the code to make it work with
your data.
This macro has to go in the sheet module of the sheet that holds the
data. Click on the sheet tab, select View Code, and paste this macro into
the displayed module. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target = "" Then Exit Sub
If Target.Column = 4 And Target.Row>1 Then
Application.EnableEvents = False
Target.Offset(, -3).Resize(, 4).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Target.EntireRow.Delete
Range("A1").Select
Application.EnableEvents = True
End If
End Sub

 

[BobH]

Thank you Otto. I am very new to Excel programming. I
used to be quite a 123 macro writer but this stuff is not
macro writing any more, it is real programming. Just
writing something that moves four cells to the left and
deletes something is a big deal. I'm learning and greatly
appreciate your help.

Bob