q2 excel help

[andrej]

Hi guys, I am stuck with a little problem for some time now and I am sure it is very easy to solve it, but I just cannot come with a working solution.

I want to use a Q2 function "-1" once I reach certain profit (I have a function for that), but I want it to work continuesly and q2 field clears after "-1" gets used.

So I am trying to write my function elsewhere but I do not know how to make it write the "-1" into q2. Is there an excel function for this (somthing that would write into some other field) or can you please help me with some other solutons or ideas?

Thank you

[osknows]

Hi Andrej,

Something like this may help

Code: Select all

Dim lastrace As String
Dim nextracetrigger as integer

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Balance as double
Dim Profit_Target as double

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)

Balance = .range("ABC1").value ' change ABC1 to whatever range has your balance
Profit_Target = .range("ABC12").value ' change ABC12 to whatever range has your profit target

If Balance >= Profit_Target And nextracetrigger = 0 Then
lastrace = .Range("A1").Value
.Range("Q2").Value = -1
nextracetrigger = 1
'.range("ABC12").value = .range("ABC12").value + X 'option to write new profit target
End If

If lastrace <> .Range("A1").Value Then
nextracetrigger = 0
End If

End With

Application.EnableEvents = True
End Sub


[osknows]

Code: Select all

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
   
   
With ThisWorkbook.Sheets(Target.Worksheet.Name)

If .Range("AA1") = 1 Then

.Range("AN5:AN35").Value = .Range("AA5:AA35").Value

End If
End With
   
Application.EnableEvents = True
End Sub


[andrej]

sorry, i'm not very good in vba. basicaly I have reduced my problem into this I need to get the value from my E2 into Q12 (originaly q2) at all times as q12 deletes once triggered.

osknows: seems to me that your last post does exactly that but I am not sure cause I know almost nothing in vba.

Thank you again

[osknows]

Sorry ignore the last post as it was in the wrong thread ??!!

[osknows]

sorry, i'm not very good in vba. basicaly I have reduced my problem into this I need to get the value from my E2 into Q12 (originaly q2) at all times as q12 deletes once triggered.

Q2 or in your case Q12 when positive sets the refresh rate or when negative selects various quick picklist options/bet refreshes.

Whenever a negative value is entered BA clears this cell to avoid continual activation. What is it you want to place in Q2/Q12 exactly?

[andrej]

sorry, i'm not very good in vba. basicaly I have reduced my problem into this I need to get the value from my E2 into Q12 (originaly q2) at all times as q12 deletes once triggered.

Q2 or in your case Q12 when positive sets the refresh rate or when negative selects various quick picklist options/bet refreshes.

Whenever a negative value is entered BA clears this cell to avoid continual activation. What is it you want to place in Q2/Q12 exactly?

I want to place -1 into q2/12. if there is -1 in e2 (in e2 I have a function to decide when to move to next market based on profit (loss) traded on each runner)

[osknows]

Code: Select all

Dim lastrace As String
Dim nextracetrigger As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)

If .Range("e2").Value = -1 And nextracetrigger = 0 Then
lastrace = .Range("A1").Value
.Range("Q2").Value = -1
nextracetrigger = 1

End If

If lastrace <> .Range("A1").Value Then
nextracetrigger = 0
End If

End With

Application.EnableEvents = True
End Sub


[andrej]

Thank you,

And I should copy thus into module? or where exactly? I know it is a stupid question probably but my knowledge of vba is close to nothing

[osknows]

Into the SHEET object of the same sheet which links to BA. Eg Sheet1, Sheet2 etc

[andrej]

THANK YOU,

works perfectly

[andrej]

well, after more testing I found a little problem here. It keeps on moving 2 markets except of one. Why is that? my trigger is based on leveled profit gained and it is not possible to have the trigger goal achieved.

[andrej]

now it jumped 3 markets

[osknows]

The code should forward only 1 market then reset once a new market is loaded. If the value in cell e2 is then -1 it will forward again. The code has been used fairly extinsively so it's more than likely cell e2 is not resetting properly when the market forwards. What formula fo you have in e2?

[andrej]

=IF(AND(OR(X14>B3;X14="");OR(X15>B3;X15="");OR(X16>B3;X16="");OR(X17>B3;X17="");OR(X18>B3;X18="");OR(X19>B3;X19="");OR(X19>B3;X19="");OR(X19>B3;X19="");OR(X20>B3;X20="");OR(X21>B3;X21="");OR(X22>B3;X22="");OR(X23>B3;X23="");OR(X24>B3;X24="");OR(X25>B3;X25="");OR(X26>B3;X26="");OR(X27>B3;X27="");OR(X28>B3;X28="");OR(X29>B3;X29="");OR(X30>B3;X30=""));"-1";"")

just this, and b3 is target leveled profit