Copy Cells To New Table If Criteria Met

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

Copy Cells To New Table If Criteria Met

Postby thegooser1 » Tue Mar 13, 2012 9:20 pm

Hi all...

I have a few different systems which can be filtered / selected using the info in a spreadsheet with the auto market.

Does anyone know of any macro that can copy the info from a column into a table on another sheet if a cell in that column contains a specified value?

This is so I can paper trail these systems for a bit.

So for example, if AW10 = 1 then copy from A10 to AW10 and add it to a table on another sheet.

Then at the end of the days racing I can check the table which will have all the data in from the days races where the AW cell had a 1 in and therefore my selections.

I have no idea what to do with macros or VBA so could really do with some help.

Any ideas?
thegooser1
 
Posts: 8
Joined: Thu Jan 05, 2012 1:37 am
Location: United Kingdom

Postby thegooser1 » Wed Mar 14, 2012 2:24 pm

OK I have got this far:

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("aw1").Column Then Exit Sub
Application.EnableEvents = False
If Target = 1 Then
Range(Cells(Target.Row, 1), Cells(Target.Row, "AX")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1#).PasteSpecial
End With
Else
Application.EnableEvents = True
Exit Sub

End If
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


The problem is it requires me to manually type the number 1 in the AW cells. What I need to do is trigger it if a criteria is met, then trigger it again if it changes back to "" and back to 1 again.

Any ideas?
thegooser1
 
Posts: 8
Joined: Thu Jan 05, 2012 1:37 am
Location: United Kingdom

Postby alrodopial » Wed Mar 14, 2012 4:52 pm

I guess that in AW1 is your formulas that gives you 1 or 0 and want data to be copied only when the value goes from 0 to 1
Try this (not tested)
Code: Select all
Dim previousVALUE as integer
Private Sub Worksheet_Change(ByVal Target As Range)


If Range("AW1").Value = 1 and previousVALUE = 0 Then
Application.EnableEvents = False
Range(Cells(Target.Row, 1), Cells(Target.Row, "AX")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1#).PasteSpecial
End With
Application.CutCopyMode = False
Application.EnableEvents = True
End If

previousVALUE = Range("AW1").Value

End Sub
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Postby thegooser1 » Wed Mar 14, 2012 4:56 pm

Hiya... thanks for your help.

thats almost there.

The code you sent will only work on a1 and only copy the 1st row. Is there any way to modify this so that if AW7 for example chnges to 1 it would copy the 7th row?

Also, this is copying the formulas over to the new sheet, is it possible to copy the values?

Thanks for any help
thegooser1
 
Posts: 8
Joined: Thu Jan 05, 2012 1:37 am
Location: United Kingdom

Postby alrodopial » Wed Mar 14, 2012 8:52 pm

In which cells do you have formulas that you want to trigger the copy-paste?
If eg cell AW5 becomes 1 you want row 5 to be copied or larger range?
Can two cells AW5,AW7 became 1 at the same time?
What do you want to happen then?
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Postby thegooser1 » Tue Mar 27, 2012 9:37 pm

Hiya mate.

What I am really after is an excel sheet / bot that can connect to gruss.

I would like it so that I can run a portfolio of systems and selections so that (for now at least) I can put a date, system, and horse. I would want the sheet to place bets as the day goes along using the quick pick lists.

But, what I would also like it to do is to be able to run without placing actual money on if the system isn't performing well but to still record the price. This way I can monitor a system (daily list of horses) without having to actually bet.

So for example:

System A has been running for 90 days. I want to bet 1% of my system bankroll on each selection throughout the day. This can be done using this sheet: http://gruss-software.co.uk/forum/viewtopic.php?t=3557

However system B has only been running for 10 days and I just want to monitor it for now before deciding to place real money on it.

So when the selections come up I want it to copy the odds (say at 3 mins) so that I can test it out. After a while I will have enough data to see if the system is going to work or not.

System C is set to bet 1% of the bankroll unless the bankroll goes under £200, then it goes into test mode. For the sake of my example this will happen half way thorogh the day so it will bet as per usual, but when the ballance drops below £200 it will go back into test mode ad just copy the odds.

I appreciate that bugger all of this makes any sence, but I am hoping that someone has something that I can use.

I am half way there. I have a sheet which will look up my selections as and when they apear. It will record the odds at 3 mins before the off and add it to the selection. I then have to put the results in manually.

I know zero about VBA, but someone made it for me so I just used a few lookups etc to get it recording which system it belongs to.
thegooser1
 
Posts: 8
Joined: Thu Jan 05, 2012 1:37 am
Location: United Kingdom


Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 9 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

cron