Running a macro from an "IF" statement
Running a macro from an "IF" statement
(OP)
I would be grateful if someone could let me know how to run a macro from an "IF" statment. I guess I am really after the syntax....
=if(a1>b1,run macro1,run macro2)
what do I need to replace "run macro" with to make it work?
thanks....
=if(a1>b1,run macro1,run macro2)
what do I need to replace "run macro" with to make it work?
thanks....
RE: Running a macro from an "IF" statement
=if(a1>b1,macro1(),macro2())
assuming no parameters passed
TTFN
RE: Running a macro from an "IF" statement
RE: Running a macro from an "IF" statement
RE: Running a macro from an "IF" statement
TTFN
RE: Running a macro from an "IF" statement
If you want the macro do to certain 'things' to the workbook - eg build a graph or whatever, then have to put if statement within the visual basic environment.
Can help if you could describe more detailed what you wish to do with the macros.
RE: Running a macro from an "IF" statement
Cheers
Greg Locock
RE: Running a macro from an "IF" statement
A thought just occured to me, is it possible to run GoalSeek within an IF statement?
Thanks for your help!
RE: Running a macro from an "IF" statement
OK here goes :
Lets say in cell "A1" you have the result of the IF test - ie true or false - not a value in this code.
In "B1" you have the cell you wish to vary such that cell "B2" equals some specific target, say 12 here.
In VBasic mode, enable the project view and double click on the sheet in which the goalseek is to operate. In the code sheet view, write :
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") Then
Range("B2").GoalSeek Goal:=12, ChangingCell:=Range("B1")
End If
End Sub
Now what happens is every time anything on that sheet changes, it checks if A1 is true and if so executes the goalseek.
But beware - if lot of calcs on the go, especially when iterating, can get very slow or some strange hangups occurring. As usual make sure you have a master copy somewhere before adding in this function!!
RE: Running a macro from an "IF" statement
That is
Function GregFun3(x1, x2, x3)
GregFun3 = x1 * x2 * x3
End Function
Function GregFun4()
Range("H15").goalseek Goal:=100, ChangingCell:=Range("H13")
GregFun4 = 999
End Function
=IF(A1>B1,GregFun3(D3,D4,D5),gregfun4())
Will execute gregfun3 correctly, but gregfun 4 merely returns the value 999, goalseek doesn't run.
Cheers
Greg Locock
RE: Running a macro from an "IF" statement
A tip : What is useful about functions is also that you can return a whole array of results - say you define a reactor or compressor model within a function, you usually want a lot of results that are carried out during the calculation procedure - just put all results into an array and return the whole lot back to the function.
eg
results(0,0) = result1
results(1,0) = result2
results(2,0) = result3
Gregfun = results
On the worksheet side, when you enter the function, highlight a range of cells large enough for the returned array, type in the function, press ctrl-shift-enter and you will be able to readily access all the returned values - each in its own cell.
RE: Running a macro from an "IF" statement
Thank you for all of your help! Your suggestion worked, with one exception. Here's what I did:
In cell A1: =If(A2>A3,"ok","no good")
In the code sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "ok" Then
Range("B2").GoalSeek Goal:=12, ChangingCell:=Range("B1")
End If
End Sub
I had to put: = "ok" otherwise I got an error. Now, if A2>A3, then goal seek runs!
Thanks again for your help!
RE: Running a macro from an "IF" statement
Just a warning though - this method has some surprising results and spectacular crashes when the workbook gets large (> 2 or 3MB) or lots of defined functions are called!
RE: Running a macro from an "IF" statement
I figured it out about 2 months ago...thanks to everyone for your comments