Cells that update automatically

[danli759]

Hi,

I want to put a certain statement into a cell if some other statement is true, but if it is not true I just want to wait until it is true (until this cell changes its value so that the condition is met)

At first I used the macro recorder, and I got this:

Range("L9").Select

ActiveCell.FormulaR1C1 = "=IF(R[-8]C[-5]<>""play"","""",""yee"")"

A few questions here:

1) Can I change the R[-8]C[-5] to just the cells name? Like just G1? I tried this but got an error. I guess this can be done?

2) What this is supposed to do is this: if cell G1 is not equal to play, then just wait until it is. I'm using a program that updates cells values in excel continuous in time(or like every 0.5 second). So at first I tried to use a While-loop but excel saw that as an infinite loop and froze and I had to break, the thing is that I want to start the macro and have it running and it might take like 30 minutes until cell G1 becomes equal to play. Excel did not like a loop that long (using while, do until, do while, etc.). So the thing is I guess to use these "" to make it wait for it. Then when G1 equals play only then do I want to put another statement into another cell, this is where ""yee"" is above. Should I just write that where ""yee"" is? If so how do I write it? In this case ""yee"" should be:

Range("L11").Select

ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-4]<1.25,""LAY"","""")"

Got an error when I tried to write this in where ""yee"" is.

I use the ActiveCell.FormulaR1C1 because I wanted to put the entire formula into the cell to be able to use "" as "loops" instead if the While-loops that just froze excel.



Sorry if this was confusing! I have never worked with cells that update automatically before!

Thanks!

/Dan

 

[GlennUK]

I think you need to explain why you are using a macro to put a formula into a cell that will calculate automatically ... and want to do that conditionally? Why don't you just put the formula into the cell and let Excel calculate the result?

 

[danli759]

I think you need to explain why you are using a macro to put a formula into a cell that will calculate automatically ... and want to do that conditionally? Why don't you just put the formula into the cell and let Excel calculate the result?

Thanks for answering!

I'm using a program to get information (odds) from a bookmaker into excel and this program updates cells in excel with the current odds. I can take in many markets in different sheets, that is why I want a macro that I can run on every sheet. So I'm doing a kind of a gambling bot here, playing a certain strategy. This strategy involves quite a few lines, so I dont want to write this every time a play, I just want to run the macro so that is goes automatically.
The program that I am using allows to write commands right into excel and do all bets there.

I can get the odds into excel even before the event has started, when the event starts then I want my strategy to start too. That is why I need a condition on when cell G1 equals "play" and when it is, then I want cell L9 to be: if(H9<=1.25;"LAY";"") How can I do this without using a While, Do Until Do While- loops?
The only way to make excel "wait" is to use these "" i guess?

Hope this makes it more clear what I want to do =)

 

[GlennUK]

And why can't you have a formula of:

=IF(G1="PLAY",IF(H9< =1.25,"LAY",""),"")

it will only do the calculation when G1 equals PLAY ... instead of using a macro to insert a formula when that is the case?

 

[danli759]

And why can't you have a formula of:

=IF(G1="PLAY",IF(H9< =1.25,"LAY",""),"")

it will only do the calculation when G1 equals PLAY ... instead of using a macro to insert a formula when that is the case?

You are right Glenn, I think I can do it this way (made it more complicated than was needed first..)

The thing is now when the event goes in play, I must save the value of the odds at this specific time exactly when cell G1 changes to "play". I need this to compare with later.
How can i save this value without it changing when the odds-cell (H9) changes?

 

[danli759]

What is mean is:

=IF(AND(G1="In-play";H4<=1,5;H4>=1,21);IF(H4<=1,25;"LAY";"");"")

Where H4 is the cell of the odds of the favourite. Here I can't have H4 everywhere because it will change during the match, I only want H4 in the last IF statement. But for: AND(G1="play";H4<=1,5;H4>=1,21) I want H4 to be the value that H4 is exactly when G1 turns to play!

How can I do this?

 

[GlennUK]

What are the contents of H4? Is it a formula?

 

[danli759]

I've managed to do what I wanted now using Worksheet_Change event.
Thanks so far for helping me Glen!

I have this:

Got one more issue with this one now.

Exactly when G1=play I want to store the value of the odds in that exact moment. I havent been able to do this, the value always changes when the odds cell changes.

The odds cells are H9 and H11 and I want to pick the one with the lowest odds when G1=play, and then freeze that value in the cell where the if statement is written, how can this be done??

 

[danli759]

I've managed to do what I wanted now using Worksheet_Change event.
Thanks so far for helping me Glen!

I have this:

Got one more issue with this one now.

Exactly when G1=play I want to store the value of the odds in that exact moment. I havent been able to do this, the value always changes when the odds cell changes.

The odds cells are H9 and H11 and I want to pick the one with the lowest odds when G1=play, and then freeze that value in the cell where the if statement is written, how can this be done??

Sorry for posting this so quickly! I've have solved it now, with... Worksheet_Change event....

 

[GlennUK]

That's great!