Code Help Please

[Steve Voltage]

I need some simple code that will put the trigger -1 into cell Q2 if cell V5 is not blank.

I've searched the gruss forum but no joy and excel/vba code hurts my brain.

I can do the above easily enough with a formula but the formula disappears when -1 is entered in Q2.

Any help please Happy

[Captain Sensible]

Dunno if you already have VBA running on your sheet but it's as simple as adding the code below if you do.

Code: Select all

If Range("V5").Value <>"" Then Range("Q2").Value = -1

You'd probably find it's better to add whatever formula you have in V5 as a VBA routine tbh as it'd avoid situations where the -1 my get duplicated

[Steve Voltage]

Hi Captain thanks for the code.

Unfortunately I cannot get it to work.
A couple of years ago I started to pick up the basics of code but I haven't used excel for awhile and although I find formulas fairly easy code remains a mystery to me.

Do I need to add worksheet calculate, application update or something?

Also good to see a name i recognise still here. You managing to still make a profit or just on here for fun/an interest?

Cheers,

Steve

[Captain Sensible]

I thought you already had VBA on the sheet, if not use something like the code below, no idea how you populate V5 but be aware if V5 is noot clear it will keep moving markets that's why it's usually best to control all your market moving code with VBA

Code: Select all


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
If .Range("V5").Value <> "" Then .Range("Q2").Value = -1
   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub


[Steve Voltage]

Mr Sensible that works a treat thank you very much. Very much appreciated and the best of luck to you sir!

[Steve Voltage]

One last question if i may. I am trying to set up a counter which counts how many times the last matched price changes.

I can do this with the following code that counts every time O5 is changed and counts in Y5:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("O5")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value <> "" Then
With Target.Offset(0, 25)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Trouble is this code only works when A1 is change manually. How would i get this code to work with BA and also to be incorporated with the other Worksheet_Change code so not to cause an error?

Many thanks,

Steve

[Captain Sensible]

Don't know what you mean by "only works when A1 is change manually" and never used intersect personally. Also my sheet shows V5 as the Averaged matched Odds so isn't ever empty so hard for me to recreate what you're trying to do.

The code below adds in a counter for when O5 changes, basically it'll store the value in a variable, if that changes it'll add 1 to Y5. It'll also clear those cells when the market move, just remove those lines if that's not what you need.

Code: Select all

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


    If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
   
   
If LastPrice <> .Range("O5").Value Then
LastPrice = .Range("O5").Value
.Range("Y5").Value = .Range("Y5").Value + 1
End If


If .Range("V5").Value<>"" Then
.Range("Q2").Value = -1
.Range("Y5").Value = ""
LastPrice = 0
End If


   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub

[Steve Voltage]

I thought you already had VBA on the sheet, if not use something like the code below, no idea how you populate V5 but be aware if V5 is noot clear it will keep moving markets that's why it's usually best to control all your market moving code with VBA

Code: Select all


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
If .Range("V5").Value <> "" Then .Range("Q2").Value = -1
   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub


Indeed you are quite correct Captain. I didn't realize at first but sometimes the code will go through the picklist with no problem but other times it will miss a selection by refreshing too quick. I have no idea why and it drives me mad!

Basically this is all i need:

When V5 is populated -1 will be entered in Q2. I have tried to write my own code using the example sheets provided by after hours i get nowhere. Vba is obviously not for me, i just cannot grasp it.

[Steve Voltage]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
If Sheet1.Cells(9, 28) = "0" Then
Sheet1.Cells(2, 17) = "2"
Else
Sheet1.Cells(2, 17) = "-1"
End If
End Sub

Ok i am getting somewhere with this but why does this the above code skip every other race in the quick pick list?