Copy Cells VB program does not work in live market

[lampac34]

hi

Anyone understand use of VB for Excel working with Betting Assistant ??

I have a coder in the USA who has written code for my excel spreadsheet to copy a line of results created by other worksheets that place a number of in running bets for me.

I have a cell that is set to "1" when the race finishes which is triggered by any of the runners being less than 1.04 (as this indicates that horse has pretty much won).

The program from this developer works perfectly when I manually set this indicator to "1", however when this cell contains a formula to set the cell to "1" and is run in the live market with lots of price data streaming in then the VB code does not work.

I have considered that may be the program does not have time to do the copy command, which copies the line of results data and then pastes as values only into another table further down on the spreadsheet so i can automatically see the results for the day without having to sit in front of my PC and copy and paste results all day.

So does anyone know why this would not work ?

Does anyone think it may be the large amount of pricing data streaming in that stops the copy from the VB code working with new price data cancels out the copy command ??

Does anyone know code to stop the spreadsheet from taking in more data so that the program has time to do the copy of the results and then turn the data connection to Betting Assistant back on ?

this must have been come across before here as the most logical thing to want to do for an automated bot is to be able to store the results. I know you can have results pasted to another sheet from Betting Assistant but I have a line of custom results indicators that I want copying.

Cheers,

Tim

[alrodopial]

Probably by entering manually the "1" the worksheet_change event is triggered and the code is executed.
It can not be triggered by a formula.
If you give a specific example someone may can help.
Can you take a look at the code or it is protected?

[lampac34]

Hi Alrodopial, thank you for reply.

No this is not the case. You CAN trigger the code from a formula in the cell as this does work, but the VB to do the copy does not work when the spreadsheet has data coming into it.

So this is not the answer I am sorry to say.

thanks for trying.

[osknows]

It's probably something simple but if you put a break point (F9) on the line that tests for "1" in your VBA code you can check the code conditions. If you can post the line of code that tests for '1' it would really help but there are a few possibilities for error

1. Code checks that cell equals 1 as an Integer, Long or Double etc but cell is actually text "'1"
2. Code uses 'FIND' method without setting LookIn:=xlValues. The 'FIND' method settings retain previously used setting values if they are not explicitly set/reset

There was a 3. but I forgot what it was mid post

You can use the actual cell value using .value2 in VBA or you could convert to numeric by multiplying cell value by *1 etc - both checking for errors

There will be an obvious solution but we need to know the formula in the cell and the VBA code checking the cell?

[lampac34]

Hi Osknows.

I am not a VB coder but I can tell you that the cell that triggers the VB code to do the copy is a formula that does work to show a value of 1 in the cell when any of the runners are less than 1.04, indicating the end or nr end of the race.

So the cell is not a value it is a formula but of course the cell does have a value that is created by the formula if you see what I mean, ie is 0 by default and 1 when race finishes. So when cell is 1 then the VB code to do the code is triggered.

This does work with a formula in the cell, however it does not work when the spreadsheet is receiving in price data from the Betting Assistant platform.

Any other ideas ? I was thinking the price data coming in every .2 mseconds is stopping the copy command or the copy command is cancelled by the spreadsheet continually refreshing with new data all the time ???

Would appreciate any help ?

[osknows]

I understand your frustration but genuinely it's difficult to help you without being able to see your spreadsheet & code. There's 1001 reasons why you get the error which we can only speculate. Either..

1. Ask your coder to investigate the problem
2. Post here the formula in the cell and the line of code (not the full code) which tests the cell
3. Email the spreadsheet to either alrodopial or myself and we can probably debug it fairly quickly

[lampac34]

I am not sure how to attach the file to a message here ? otherwise I can send the spreadsheet to you which has the VB code built in.

If you send me your email address I can email the spreadsheet to you ?

Cheers

Tim

[lampac34]

Yesterday the forum member who goes by the nickname of "Osknows" helped with a VBA script that both myself and a very competent programmer in the USA were having problems with.

He resolved the issue within less than half an hour !

I now have bespoke results generated automatically within my Excel sheets which my programmer could not get to work while the Betting Assistant platform was feeding my worksheet with live price data.

I am used to farming projects out to both web developers and programmers and I am both impressed by Osknows and exceptionally grateful to him for his brilliant and fast feedback and clearly superior VBA knowledge.

Thank you Osknows. I can easily recommend your services to any one requiring bespoking of their betting bot spreadsheets on this forum.

[osknows]

No probs & thanks for the free advertising

[lampac34]

No problem Os - you deserve it. Thanks again.