Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for running a macro from an =IF function?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for running a macro from an =IF function?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for running a macro from an =IF function?
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 -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for running a macro from an =IF function?
Bob
Here is an event macro that reacts when an entry is made in column D. The macro copies Columns A:D 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.Row1 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" wrote in message ... 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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for running a macro from an =IF function?
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 -----Original Message----- Bob Here is an event macro that reacts when an entry is made in column D. The macro copies Columns A:D 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.Row1 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" wrote in message ... 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 -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a Macro from a Spreadsheet Function | Excel Worksheet Functions | |||
COUNTA function causing syntax error in macro... | Excel Worksheet Functions | |||
Running a Macro that enters a function that returns #VALUE! | Excel Discussion (Misc queries) | |||
How can I invoke running a macro from within an "IF" function. | Excel Worksheet Functions | |||
Macro syntax error when running query | Excel Programming |