VBA Help...

[mak]

Hi
I am trying to "write" some code and need some help...

Private Sub Worksheet_Change(ByVal Target As Range)

r = 79 '(Column CA)
Z = 17 ' (Column Q)

For fx = 5 To 55
If Cells(r, fx).Value = "Selection" Then
Cells(Z, fx).Value = "BACK"
End If
Next fx
end sub

Hopefully if CA5=Selection then Q5=BACK
or if CA6=Selection then Q6=BACK

I am trying to test it without log in BA and the above code is doing nothing...

[osknows]

Hi mak,

should be Cells(row,col)


Private Sub Worksheet_Change(ByVal Target As Range)

r = 79 '(Column CA)
z = 17 ' (Column Q)

For fx = 5 To 55
If Cells(fx, r).Value = "Selection" Then
Cells(fx, z).Value = "BACK"
End If
Next fx
End Sub

[osknows]

This might be a little quicker though....

Private Sub Worksheet_Change(ByVal Target As Range)

rng_check = Range("CA5:CA55").Value

For i = LBound(rng_check) To UBound(rng_check)
If rng_check(i, 1) = "Selection" Then
rng_check(i, 1) = "BACK"
Else
rng_check(i, 1) = Empty
End If
Next i

Range("Q5:Q55").Value = rng_check


End Sub

[mak]

Os hi,
i was working off line so i just so your response (Thanks! As you can see i am trying to learn some VBA)

Meanwhile i worked a little more (a bit proud for myself today) and i came up with the following code which is working

Private Sub Worksheet_Change(ByVal Target As Range)




r = 79 '(Column CA)
Z = 17


For fx = 5 To 55



If (Cells(fx, r).Value = "Selection") And (Cells(fx, r + 1).Value <> "OK") Then
Cells(fx, Z).Value = "BACK"
End If


If Cells(fx, Z).Value = "BACK" Then
Cells(fx, r + 1).Value = "OK"
End If


If (Range("cg1") <= 60) And Cells(fx, 85).Value = 1 And Cells(fx, r + 2).Value <> "OK_LAY" Then
Cells(fx, 20).Value = ""
Cells(fx, Z).Value = "LAY"
End If

If Cells(fx, Z).Value = "LAY" Then
Cells(fx, r + 2).Value = "OK_LAY"
End If


Next fx

but now i want to add something like the following

For ax = 5 To 55
If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value
Cells(ax, 17).Value = ""
Cells(ax, 80).Value = ""
Cells(ax, 81).Value = ""
End If
Next ax

I want to clear some flags when i move to next market...
here it crashes

I also trying to add
If Target.Columns.Count = 16 Then
Application.EnableEvents = False

code....

Application.EnableEvents = True
end if

but i can't ...

Any ideas?

[osknows]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Columns.Count = 16 Then
Application.EnableEvents = False

r = 79 '(Column CA)
Z = 17


For fx = 5 To 55

If (Cells(fx, r).Value = "Selection") And (Cells(fx, r + 1).Value <> "OK") Then
Cells(fx, Z).Value = "BACK"
End If


If Cells(fx, Z).Value = "BACK" Then
Cells(fx, r + 1).Value = "OK"
End If


If (Range("cg1") <= 60) And Cells(fx, 85).Value = 1 And Cells(fx, r + 2).Value <> "OK_LAY" Then
Cells(fx, 20).Value = ""
Cells(fx, Z).Value = "LAY"
End If

If Cells(fx, Z).Value = "LAY" Then
Cells(fx, r + 2).Value = "OK_LAY"
End If
Next fx



If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value
Cells(fx, 17).Value = ""
Cells(fx, 80).Value = ""
Cells(fx, 81).Value = ""
End If


Application.EnableEvents = True
End If


End Sub


[mak]

Os

it still doesn't clear the flags

If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value
Cells(fx, 17).Value = ""
Cells(fx, 80).Value = ""
Cells(fx, 81).Value = ""
End If


Application.EnableEvents = True
End If

I made so many combination's till now, that i got headache

[alrodopial]

Try this:

Code: Select all

Dim lastrace

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False

r = 79 '(Column CA)
Z = 17


For fx = 5 To 55

If (Cells(fx, r).Value = "Selection") And (Cells(fx, r + 1).Value <> "OK") Then
Cells(fx, Z).Value = "BACK"
End If


If Cells(fx, Z).Value = "BACK" Then
Cells(fx, r + 1).Value = "OK"
End If


If (Range("cg1") <= 60) And Cells(fx, 85).Value = 1 And Cells(fx, r + 2).Value <> "OK_LAY" Then
Cells(fx, 20).Value = ""
Cells(fx, Z).Value = "LAY"
End If

If Cells(fx, Z).Value = "LAY" Then
Cells(fx, r + 2).Value = "OK_LAY"
End If
Next fx



If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value
For fx = 5 To 55
Cells(fx, 17).Value = ""
Cells(fx, 80).Value = ""
Cells(fx, 81).Value = ""
Next fx
End If


Application.EnableEvents = True
End If


End Sub


[mak]

Hi Al
again is not working (as i need to)
actually is working contentiously so it can't place the bets etr..

[excelhasey]

Can I ask you guys what this code will actually do for you ??

[mak]

hi
it is more an attempt to learn vba
what it does (with some formulas in my excel sheet it just place a dummy back bet and it close the position - with a lay bet - 60 seconds before the race start.

At the time is working as it should, but when it goes to next the market some flags ("OK" "OK_LAY etr) should be deleted in order the code to work again..

As Os and Al could add is very amateur & inefficient code, but i have to begin from somewhere...

[alrodopial]

working contentiously so it can't place the bets etr..

???

[mak]

If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value
For fx = 5 To 55
Cells(fx, 17).Value = ""
Cells(fx, 80).Value = ""
Cells(fx, 81).Value = ""
Next fx
End If

this part of code keeps deleting the flags & the triggers (looping)

[alrodopial]

Did you put the

Dim lastrace

before the start of the code?

[mak]

Did you put the

Dim lastrace

before the start of the code?

No i didn't saw it
But now i test it and it is working!!

What a difference it makes...
I had test it with a Dim lastrace as string (just a copy from another Os code)
and didn't work either...

It is very hard for me to understand this variables.

Thanks a lot Al

* i am sure i will bother you & probably Os later

[alrodopial]

It is very hard for me to understand this variables.

It's also for me.
That's why you declare it without specifying it and let VBA to find what kind of variable it is.
The correct syntax is
Dim lastrace as string
but you must declare it outside of the sub.
This way it is not deleted at the end of the sub.
If it is declared inside of the sub it will be created at every running of the code but also it will be deleted at the end of the sub.

My knowledge reaches this point and does not go further