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

[Bob Phillips]

Why not put the formula in the cell, you don't need VBA.

[danli759]

Why not put the formula in the cell, you don't need VBA.

Thanks for the answer!
But I dont see how this could be done. How do I write the next statement into the If statement, I write If(G1="play";?;"") into a cell then the questionmark should be: If(H9<1.25;"LAY";"") WRITTEN IN ANOTHER CELL than the cell where If(G1="play";?;"") is written. How do I do this without using VBA? Or how do I do it using VBA?

But this is just the beginning of what I want to do, there are way more lines, that's why I want to have it as a macro to be able to just run the macro in several sheets in excel and then walk away from the computer. (I'm doing a kind of trading bot)

Thanks!
/Dan

[Bob Phillips]

If you want the cell to change when G1 changes to Play, you should use Worksheet_Change event code.

[danli759]

If you want the cell to change when G1 changes to Play, you should use Worksheet_Change event code.

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

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.

I have this: =IF(G1="play";IF(H9<H11;H9;H11);"")

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 much xld for helping me!

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.

I have this: =IF(G1="play";IF(H9<H11;H9;H11);"")

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....

[Bob Phillips]

I thought I had posted suggesting you do exactly that, but it seems it didn't happen

[danli759]

I thought I had posted suggesting you do exactly that, but it seems it didn't happen

Thanks for the help anyway, I really appreciate it!

[CatDaddy]

can use cells name by:

[vba]Range("A1").Address(ReferenceStyle:=xlR1C1)[/vba]

and application.screenupdating = false might make your program run a little faster