Quick VBA fix for someone who know what they are doing

[Toma]

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]

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

[Captain Sensible]

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

[Toma]

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

Cheers