run macro by a condition located in a cell

[dikken20]

Hi,

I have a macro.
I would like to run the macro each time a condition (that located in a specific cell in my excel sheet) set to true.

I managed to do so, but there were some problems:
1. It ran only once (and the second time the condition set to true again, the macro didn't run).
2. If I set the condition to be inside the macro, then the macro needed to run all the time and I got into unlimited loop. Therefore I cannot use the excel sheet unless I stop the loop,meaning, I need to run the macro manually each time.

I wish I could have a simple way like:

(In a specific cell A2) - "= If (A1=true,RunMyMacro,)"
so each time A1 set to true then my macro will run - BUT only once, then when A1 set to false and back to true it will run the macro again automatically.

HELP!!
Can someone give me a hand please ?

BIG Thanks for your time and any reply.

 

[Andrew Poulsom]

Have you tried using the Worksheet_Change event procedure?

http://www.cpearson.com/Excel/Events.aspx

 

[lenze]

Hi and welcome to the board!!!
How are you setting the condition!!
Manually, Formula, Code??
Is True/False the only options for the cell??
lenze

 

[dikken20]

Hi Andrew,lenze
Thanks for the VERY fast replies!

Andrew,
I haven't tried yet.. I will read CPearson's post in a minute. I'm in my very first steps of macros and VBA.

lenze,
The condition itself is a "standard" condition of excel which I would like to have. (i.e. as I wrote in my post).
The True\False I meant, is not just True\False (Boolean) options. Meaning, for all it matters 2 is equal to 1+1, but the computer understand that equation as true or false, and that was my meaning. In other words, the condition itself is about time (if the time now is 16:30, then run the macro).

 

[lenze]

OK: To get you started!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
If Target = "your True condition" Then Call yourMacro
End Sub

That said, I really can't see what you are doing. Can you provide more detail? How is time entered?? Are you implying you want the macro to run at specific times. What is in your cell now?? A formula? A Blank?

lenze

lenze

 

[dikken20]

Well, I tried to ask in general to spare your time, but I will go specificlly.
In my worksheet I've got:
1. I have a cell which show me the time (HH:MM:SS, using RTD server - it shows me the exact time automatically).
2. four columns, B,C,D,E and 200 rows each. On column B I've got 200 different numbers that I retrieve online from outside software (also by RTD server).
On column C I've got the 200 numbers that I retrieved yesterday, on column D the day before and same for column E.
3. My macro, which when I run it - it copies first column D into column E, then C into D, then B into C.
My specific question is actually for finding a way to run this macro when the time in the cell of point #1 is equal to whatever time I wish (mostly it's 16:30:00).

Hope now I'm specifically and clear enough
Thanks again!

 

[dikken20]

Hmm...
Any suggestions ?

 

[HRIS]

Is the main goal then to have the macro run at the same specific time each day? If so you might check out this link: http://www.ozgrid.com/Excel/run-macro-on-time.htm.

If you need to change the time to run periodically, perhaps there is a way to use this code and then point it to your cell to get the time?

 

[dikken20]

HRIS,
Thanks for your reply.

To be honest, that is what I've done so far. but there were 2 problems:

1. At the first day it ran ok, but at the day after it didn't run.

2. The numbers that I get online are a numbers of a stock market exchange.Therefore, the market is not always open. usually its opened 5 days in a week and sometimes less. So I need it to run almost everyday - but not every day. I do have an indication on one of the cells in my worksheet that tells me whether the market is open or close (in that cell I get a string of "Yes" if open, "No" if closed).