Help with Gruss

[summit]

My friend and I are trying to sort this. He is the expert on VBA - but if we have missed something simple, please let me know.

[summit]

I will try and check this and let you know asap.

But as I mentioned earlier, when we enter it manually in A2, H2 value moves to H4 and the new H2 value/Stake is calculated with the macro a few seconds later.

I have noted your concerns about the circular references as well and will discuss with him asap.

We just cannot work out why we cannot recognise the change in A2 when results are automatic?

[Captain Sensible]

And your worksheet_change routine is under the Results sheet not under Sheet1, because your code only references cells H2 etc it'd mean it'll work when run manually on whichever sheet is selected.

[summit]

"Have you checked that your worksheet_change routine is entered under the correct worksheet?"

I will check with my colleague - who is the VBA expert and let you know asap.

We just cannot work out why it does not recognise the automatic results entries and yet does work when we input a change manually?

[Captain Sensible]

Also is H2 calculated with a macro or with a formula in the cell H2? If it's written with a macro you could just simply change your sub to

Code: Select all

Sub shiftrow()

Range("H2:H3").Insert (xlShiftDown)

End Sub

Or simply include the line

Code: Select all

Worksheets("RESULTS").Range("H2:H3").Insert (xlShiftDown)

in your macro that calulates the H2 value

[summit]

Thank you so much again.

I will test all of these and get back tomorrow.

[Captain Sensible]

I think the easiest thing to do is just include the line

Worksheets("RESULTS").Range("H2:H3").Insert (xlShiftDown)

within the code you're using to calculate the vaue of H2. You'd just fire it before entering the new value in H2

[summit]

Hi again,

We still cannot sort this shift down trigger problem. It appears that it may be an active sheet issue. Even though the bets are placed at 20 seconds before the off, they are not transferred to the results page until the race starts and when the script looks for the results sheet, it cannot find it?

I am not sure if this is correct, or even makes sense to anyone on the forum?

But we still cannot work out why worksheet_calculate () event is not working for calculation using script imported data.

Are we missing something?

Any ideas...please!!!

Thank you for looking

[Captain Sensible]

I don't use the results sheet myself but the help files show it only gets updated once the program moves onto the next race in the quickpicklist.

So if your coding doesn't have full cell addresses or the code is under sheet1 rather than results it won't run. And even with the coding under the correct sheets it may not show the result of the race if you move on too early. Thats why I'd suggest you use full references and include your shiftdown code within your bet placement coding and not under the results sheet as it won't run or update when you actually need it.

If it was me I'd amend your code to enter -6 in the Q2 cell at 25 seconds to the off as that forces the results sheet to update so you'd have latest results, calculate your stakes, shiftdown H2 to H4 using the full address Worksheets("RESULTS").Range("H2:H3").Insert (xlShiftDown) and then enter your new value for H2 into the sheet Worksheets("RESULTS").Range("H4").Value=new_stake

[Captain Sensible]

I don't use the results sheet myself but the help files show it only gets updated once the program moves onto the next race in the quickpicklist.

That's a single update by the way not on every refresh

[summit]

Hi Captain,

Thank you very much again for your suggestion. We have implemented it and now have the rows moving down as required.

Thank you very much!!

It's still a work in progress, but we have made giant steps forward with your help. Watch this space. Thanks you.