can run vba script in step by step but does not run on auto

[paddycorcoran]

Hi,

I am trying to run the following VBA script against the Bet Angel Professional template and all it does is checks the weight of money on the back side against the weight of money on the lay side...If the back is larger,display 123 in cell G45,otherwise display 456 in G45..It works fine stepping through,but when I hit the run button,the displayed weights of money are correct,but the G45 cell never changes when the weight of money shifts from one side to the other..

Please help...I have tried Doevents,put in time delays etc,but nothing works!


Sub bob()
For i = 1 To 10000
ActiveSheet.Range("G43").Formula = "=sum(E10:G10)"
ActiveSheet.Range("H43").Formula = "=sum(H10:J10)"
If ActiveSheet.Range("G43") > ActiveSheet.Range("H43") Then

ActiveSheet.Range("G45").Value = 123
Else: If ActiveSheet.Range("G43") <= ActiveSheet.Range("H43") Then ActiveSheet.Range("G45").Value = 456
End If
Rem Sleep 500&
Next i
End Sub

[paddycorcoran]

any help att all would be nice

[paddycorcoran]

any help at all.......

[nonails]

I cant help you on vba I`m only learning myself. I have come across references on two other forums which may help you, 1 is the punters lounge and the other is uk betting tips. I get stuck on these myself.
nn

[nigelk]

Enter the formulas in the worksheet as normal
then try putting your code in as a worksheet calculate event and not in a module.



Private Sub Worksheet_Calculate()

If Range("G43") > Range("H43") Then
Range("G45").Value = 123
Else
Range("G45").Value = 456
End If


End Sub

[paddycorcoran]

Hi Nigelk

Thanks for that suggestion and snippet of VBA.

However,I am seeing the same problem...it run in step by step mode but then when I run the whole program,everything freezes as before...
even if I try Alt-F11,nothing happens while the program is running.
Its a problem I cant get around...Ive tried nearly everything.No matter what kind of a loop I use,when I run the program,the screen freezes..
Im using excel 2007.

P

[nigelk]

have you got any code in a worksheet change event?

[paddycorcoran]

Thanks nigelk...

Im just investigating that very thing at the moment

P

[nigelk]

Hi

It probably just the loop itself, I've put a message box that pops up when its finished running:

Sub bob()
For i = 1 To 10000
ActiveSheet.Range("G43").Formula = "=sum(E10:G10)"
ActiveSheet.Range("H43").Formula = "=sum(H10:J10)"
If ActiveSheet.Range("G43") > ActiveSheet.Range("H43") Then

ActiveSheet.Range("G45").Value = 123
Else: If ActiveSheet.Range("G43") <= ActiveSheet.Range("H43") Then ActiveSheet.Range("G45").Value = 456
End If

Next i

MsgBox "ok"
End Sub

It will 'freeze up' while it's running, if you add the msgbox, you'll see how long it takes.

You shouldn't need to use a loop, a formula in itself would be ok, or the code I posted earlier will replace
the loop code.

In your loop, you are entering the same formula 10,000 times.

[paddycorcoran]

Hi nigelk

I really appreciate your post.Thanks for the help on this..the reason Im looking for the loop is that the values of the two cells I am testing are constantly changing..so I think you were right earlier in saying that I would need to use the Worksheet_change..
What I am trying to do is count up the total of the money in cells E:10->G:10,the BACK weight of money and test it against the H:10 -> J:10,the LAY weight of money and then put in a back bet when Lay money is highest and put in a lay bet when the back money is highest.
So what Im trying here is to display 123 when BACK is higher,and 456 when LAY is higher....
Im new to VBA but I would have thought if I could loop,maybe at a slower rate,see the cell value change from 123 to 456 and back when the weight changes.
Im running this in a module off the Bet Angel Sheet 1...
Do you see what Im trying to do...maybe Im jumping in at the deep end

Thanks

P

[paddycorcoran]

Hi nigelk...

I think I have it,,,I think it just needs the code to run under the Bet Angel worksheet.
Its looking good now.
And with no loop! I wonder how that works...

Thanks

P