Quick VBA fix for someone who know what they are doing

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

Moderator: 2020vision

Quick VBA fix for someone who know what they are doing

Postby Toma » Mon Oct 08, 2018 6:44 am

Guys,

Wonder if someone can help me.

I am trying to capture the last traded prices when a certain amount is traded in the market, I only want to capture the prices once and thats it.

I have been looking around the forum and found some code BUT it get some sort of runtime stack error when I use it?

I have no idea what I am doing with VBA so apologies for that but this is the code I am using...

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range("B3").Value >= Range("AE2").Value Then
Range("O5:O50").Copy
Range("AC5").PasteSpecial (xlPasteValues)
Else
End If

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Basically I wan't to copy the last prices matched at the time the matched amount hits my criteria (AE2) and thats it.

Any help would be great
Toma
 
Posts: 74
Joined: Sat Nov 19, 2005 9:06 pm
Location: Wallsend, Newcastle upon Tyne

Re: Quick VBA fix for someone who know what they are doing

Postby Toma » Mon Oct 08, 2018 6:51 am

Also, I would like it when it loads the next market IF the required amount isn't matched to clear cells AC5:AC50.

I have no idea how to code that though.

Cheers
Toma
 
Posts: 74
Joined: Sat Nov 19, 2005 9:06 pm
Location: Wallsend, Newcastle upon Tyne

Re: Quick VBA fix for someone who know what they are doing

Postby Captain Sensible » Mon Oct 08, 2018 1:09 pm

Toma wrote:Also, I would like it when it loads the next market IF the required amount isn't matched to clear cells AC5:AC50.

I have no idea how to code that though.

Cheers



Your code was pretty much there, I haven't tested this so check it all works before using but something like this shoould be fine. If there's any bits that are confusing to you just ask. Basically the MyMarket bit is being used to clear old data when you enter a new market. With Target.Parent just mean we don't have to fully reference the cells, say for instance you were looking at stuff on Sheet2 when the macro triggered if we didn't have parent target all vba would happen on sheet2.

Code: Select all
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant

    If Target.Columns.Count <> 16 Then Exit Sub  ' ensures we only trigger the VBA on a price refresh
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    With Target.Parent


If .Range("A1").Value <> MyMarket And .Range("AC5").Value <> "" Then .Range("AC5:AC50").Value = ""

If .Range("B3").Value >= .Range("AE2").Value And .Range("A1").Value <> MyMarket Then
.Range("AC5:AC50").Value = .Range("O5:O50").Value
MyMarket = .Range("A1").Value
End If


   
     
   
    End With


    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Quick VBA fix for someone who know what they are doing

Postby Toma » Mon Oct 08, 2018 5:49 pm

Thanks very much Captain - this is exactly what I wanted very much appreciated

Cheers
Toma
 
Posts: 74
Joined: Sat Nov 19, 2005 9:06 pm
Location: Wallsend, Newcastle upon Tyne


Return to Find an Excel developer

Who is online

Users browsing this forum: Bing [Bot] and 5 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.