vba help

[mak]

can someone post an example how to make some calculations in vba and if conditions are met to place a LAY bet?

for example (I have the a function which calculates the ticks difference between back and lay odds name = getticks
so
how can I have vba to calculate the differnce between f5 h5 cells until the last used row or until row 50 for example and if is less than 10 ticks to place LAY bet in the correct selection?

thanks

[Captain Sensible]

If statements in VBA aren't too disimilar to excel ones, if anything they're easier to understand when you're writing them down than formulas. Plus you can use all the Or And etc commands to link them together. Here was a condensed version of your switching market routine i did whist looking at the other thread yesterday

Code: Select all

If (.Range("E2").Value = "In Play" And .Range("F2").Value <> "") Or .Range("F2").Value = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = .Range("A1").Value
.Range("Q2").Value = -1

Else
If .Range("A1").Value <> currentMarket Then marketChanging = False

End If

As you can see it's basically If this do that, otherwise do this.

To find the difference between F5 and H5 using getTicks you'd simply put

Code: Select all

=getTicks(Range("F5").Value, Range("H5").Value)

Then just use an If statement if it's over 10 to fill in Q5, R5 and S5

[Captain Sensible]

What we need now is someway to read and loop all the market data, as you can imagine there are plenty of different ways to acheive this aim but all rely on us finding the last cell with data. In order not to complicate things I'll post the code Osknows gave me, I'm sure he'll be OK with it being posted.

OK so we need a function to find the last cell so we stick the following function into a module so it can be accessed anywhere from the code

Code: Select all

'**PUT THIS IN A MODULE SO IS AVAILABLE TO ALL CODE
Public Function LastRow(ByVal rng As Range, Optional Offset As Long) As Long
'The most accurate method to return last used row in a range.
On Error GoTo blankSheetError
    'Identify next blank row
    LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + Offset

    'On Error GoTo 0 'not really needed
    Exit Function

blankSheetError:
    LastRow = 1 'Will produce error if blank sheet so default to row 1 as cannot have row 0
    Resume Next

End Function

[mak]

i tried the following but it does not working. where is the fault?
--------------------------------------------------------------------------------------------------------

Code: Select all

If GetTicks(Range("F5").Value, Range("H5").Value) >= 80 Then
    Cells("Q5").Value = "LAY"
End If

Also is there any easiest way to address all rows? (f6 , f7 ect)
I have seen some code like
for fx 5 to 40
if cells(fx,8)>=80 then
cells(fx,17).value="LAY"

something like this I am trying to get

[Captain Sensible]

I've no idea where you get Cells("Q5").Value from try using Range("Q5").Value

Once you get the last row of data using

Code: Select all

rowFindLast = LastRow(.Range("A:P"), 0)

you can now loop thru the data as we know the start and end boundaries and just use For that lets us loop like so

Code: Select all

        For i = 5 To rowFindLast
       Range("S" & i ).Value="Something or other"
        Next i

[Captain Sensible]

Might be an idea for you to look up how to reference cells within excel before going further as you seem to have mixed up various ways of accessing them , have a look at https://msdn.microsoft.com/en-us/librar ... =office.11).aspx it'll explain how to refernce cells using index numbers and ranges. You'll need to understand those if you'll be using arrays

[Captain Sensible]

https://msdn.microsoft.com/en-us/librar ... 21357.aspx

[alrodopial]

Geia sou Maki

In order to be helped you have to be more accurate in your descriptions
The below does not help
Do you get nothing? Do you get error? What kind of error? etc

i tried the following but it does not working. where is the fault?

[mak]

i have the code below this line. captain answered the question.

now I am having a new one
I thought to start with the following
When I test it (manual change cell h5, so condition are met) it works ok but the excel freezes
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F5").Value >= 5 And Range("F5").Value <= 40 Then '( I don't why or how to continue with AND)

If GetTicks(Range("F5").Value, Range("H5").Value) >= 15 Then
Range("R5").Value = minusTicks(Range("h5"), 1)
Range("Q5").Value = "LAY"

End If
End If

End Sub

[Captain Sensible]

It's because you haven't turned events off during the code, so the code is simply going into a continuous loop, you change a cell that kicks off the macro that changes a cell that kicks off the macro etc etc

change it to the following and it turns off the events until the code has fully run

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("F5").Value >= 5 And Range("F5").Value <= 40 Then '( I don't why or how to continue with AND)

If GetTicks(Range("F5").Value, Range("H5").Value) >= 15 Then
Range("R5").Value = minusTicks(Range("h5"), 1)
Range("Q5").Value = "LAY"

End If
Application.EnableEvents = True
End If

[Captain Sensible]

Here's some code so you can add all the bits together and use arrays, remember to add the rowFindLast function in the 3rd post to your module otherwie it won't work. It'll show you how to grab an array and use your getTicks function

Code: Select all

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long
 Application.EnableEvents = True
        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False

        With Target.Parent
               
            rowFindLast = LastRow(.Range("A:S"), 0) 'get the last row
            readArray = .Range("A1:S" & rowFindLast).Value 'put our data into an array
            .Range("S1").Value = 6
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               
               
            If getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) > 1 Then
            readArray(i, 19) = "over 1 tick"
            readArray(i, 18) = getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) 'display ticks
            Else
            readArray(i, 19) = "under 1 tick"
            readArray(i, 18) = "" ' we clear cell incase it still holds data from previous refresh
            End If
   
           
            Next i
           
           .Range("A1:S" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
       
        End With
        Application.EnableEvents = True
    End Sub


[mak]

Captain
what CCur is?

[mak]

and why "S" range? Just to understand

For the time being I understand just half of the code you post it. and If I test it it does not alter anything in the excel

[Captain Sensible]

Captain
what CCur is?

CCur changes the array value to a currency type because your getTicks function expects the value to be currency not variant

I grabbed the data to the "S" range as that covers your trigger cells.

I have no idea why it's not working for you as I'm not there. Do you have it linked to Betting Assistant ? If not comment out the line

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub

and you'll be able to play with things manually.

Do you have the functions in your modules?

Are events turned off on your instance of excel due to an error?

[Captain Sensible]

Try using this code as it will show you the state of your events to confirm if the worksheet change rouitine is working in cell S1, it'll show as TRUE or FALSE. It should be displaying the ticks in columns R and S if working

Code: Select all

  Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long

        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False
         Range("S1").Value = Application.EnableEvents
        With Target.Parent
               
            rowFindLast = LastRow(.Range("A:S"), 0) 'get the last row
            readArray = .Range("A1:S" & rowFindLast).Value 'put our data into an array
         
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               
               
            If getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) > 1 Then
            readArray(i, 19) = "over 1 tick"
            readArray(i, 18) = getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) 'display ticks
            Else
            readArray(i, 19) = " 1 tick"
            readArray(i, 18) = "" ' we clear cell incase it still holds data from previous refresh
            End If
   
           
            Next i
           
           .Range("A1:S" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
       
        End With
        Application.EnableEvents = True
       Range("S1").Value = Application.EnableEvents
    End Sub