greenup.xls

[andrej]

Hi all, I have just modified the code in greenup.xls to fit my needs but I get an error message here:

rivate Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
Dim i As Integer
For i = 1 To UBound(pl)
If betType = "BACK" Then
If i = r Then
pl(i, 1) = pl(i, 1) + (stake * (odds - 1))
Else
pl(i, 1) = pl(i, 1) - stake
End If
Else
If i = r Then
pl(i, 1) = pl(i, 1) - (stake * (odds - 1))
Else
pl(i, 1) = pl(i, 1) + stake
End If
End If
Next
End Sub

I cannot figure out what this part of the code does so I have no idea what is wrong.

I get a type mysmatch error. I have modified the rest of the code to write into different fields than the ones in the original code.
Basicaly I have moved it to write to AB, AC, AD, AE instead of Y, Z, AA, AB.

In total I have changed this

Code: Select all

Private Sub calcGreenUp()
    Dim r As Integer, i As Integer
    Dim selecName As String
    Dim stake As Currency, odds As Currency, win As Currency, lose As Currency, diff As Currency, betType As String
    Dim idx As Integer
    Dim myBetsRange As Range
    r = 2
    ReDim ifWin(100)
    ReDim ifLose(100)
    Set myBetsRange = Worksheets("MyBets").Cells
    If currentMarket <> Cells(1, 1) Then initMarket
    pl = plRange
    currentMarket = Cells(1, 1)
    Do
        ' calculate win and lose positions
        If myBetsRange.Cells(r, 5).Value = "F" Then
            selecName = myBetsRange.Cells(r, 2).Value
            idx = getIndex(selecName)
            If idx <> -1 Then
                stake = myBetsRange.Cells(r, 3).Value
                odds = myBetsRange.Cells(r, 4).Value
                If myBetsRange.Cells(r, 6).Value = "B" Then
                    ifWin(idx) = ifWin(idx) + (stake * (odds - 1))
                    ifLose(idx) = ifLose(idx) - stake
                Else
                    ifWin(idx) = ifWin(idx) - (stake * (odds - 1))
                    ifLose(idx) = ifLose(idx) + stake
                End If
            End If
        End If
        r = r + 1
    Loop Until myBetsRange.Cells(r, 1).Value = ""
    ' calculate green up stakes
    For r = 5 To 50
        getIfWinLose Cells(r, 1).Value, win, lose
        If win = lose Then
            betType = ""
            stake = 0
            odds = 0
        End If
        If win > lose Then
            betType = "LAY"
            diff = win - lose
            odds = Cells(r, 8).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If win < lose Then
            betType = "BACK"
            diff = lose - win
            odds = Cells(r, 6).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If stake < 0.01 Then
            stake = 0
            odds = 0
            betType = ""
        End If
        Cells(r, 25).Value = betType
        Cells(r, 26).Value = stake
        Cells(r, 27).Value = odds
        calculateFuturePL r - 4, betType, stake, odds
    Next
    levelPLRange = pl
End Sub

Private Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
    Dim i As Integer
    For i = 1 To UBound(pl)
        If betType = "BACK" Then
            If i = r Then
                pl(i, 1) = pl(i, 1) + (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) - stake
            End If
        Else
            If i = r Then
                pl(i, 1) = pl(i, 1) - (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) + stake
            End If
        End If
    Next
End Sub

Private Sub initMarket()
    Dim r As Integer
    Set selecIndex = New Collection
    r = 5
    Do
        r = r + 1
    Loop Until Cells(r, 1) = ""
    Set plRange = Range(Cells(5, 24), Cells(r - 1, 24))
    Set levelPLRange = Range(Cells(5, 28), Cells(100, 28))
    levelPLRange = ""
    Set levelPLRange = Range(Cells(5, 28), Cells(r - 1, 28))
End Sub

Private Function getIndex(selecName As String) As Integer
    Dim idx As Integer
    Dim r As Integer
    Dim found As Boolean
    On Error GoTo index_not_found
    idx = selecIndex(selecName)
    getIndex = idx
    Exit Function
index_not_found:
    On Error GoTo 0
    r = 4
    found = False
    Do
        r = r + 1
        If Cells(r, 1).Value = selecName Then
            selecIndex.Add r - 5, selecName
            found = True
        End If
    Loop Until found Or Cells(r, 1).Value = ""
    If found Then getIndex = r - 5 Else getIndex = -1
End Function

Private Sub getIfWinLose(selecName As String, ByRef win As Currency, ByRef lose As Currency)
    Dim idx As Integer
    On Error GoTo index_not_found
    idx = selecIndex(selecName)
    win = ifWin(idx)
    lose = ifLose(idx)
    Exit Sub
index_not_found:
    On Error GoTo 0
    win = 0
    lose = 0
End Sub

to this

Code: Select all

Private Sub calcGreenUp()
    Dim r As Integer, i As Integer
    Dim selecName As String
    Dim stake As Currency, odds As Currency, win As Currency, lose As Currency, diff As Currency, betType As String
    Dim idx As Integer
    Dim myBetsRange As Range
    r = 2
    ReDim ifWin(100)
    ReDim ifLose(100)
    Set myBetsRange = Worksheets("MyBets").Cells
    If currentMarket <> Cells(1, 1) Then initMarket
    pl = plRange
    currentMarket = Cells(1, 1)
    Do
        ' calculate win and lose positions
        If myBetsRange.Cells(r, 5).Value = "F" Then
            selecName = myBetsRange.Cells(r, 2).Value
            idx = getIndex(selecName)
            If idx <> -1 Then
                stake = myBetsRange.Cells(r, 3).Value
                odds = myBetsRange.Cells(r, 4).Value
                If myBetsRange.Cells(r, 6).Value = "B" Then
                    ifWin(idx) = ifWin(idx) + (stake * (odds - 1))
                    ifLose(idx) = ifLose(idx) - stake
                Else
                    ifWin(idx) = ifWin(idx) - (stake * (odds - 1))
                    ifLose(idx) = ifLose(idx) + stake
                End If
            End If
        End If
        r = r + 1
    Loop Until myBetsRange.Cells(r, 1).Value = ""
    ' calculate green up stakes
    For r = 5 To 50
        getIfWinLose Cells(r, 1).Value, win, lose
        If win = lose Then
            betType = ""
            stake = 0
            odds = 0
        End If
        If win > lose Then
            betType = "LAY"
            diff = win - lose
            odds = Cells(r, 8).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If win < lose Then
            betType = "BACK"
            diff = lose - win
            odds = Cells(r, 6).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If stake < 0.01 Then
            stake = 0
            odds = 0
            betType = ""
        End If
        Cells(r, 28).Value = betType
        Cells(r, 29).Value = stake
        Cells(r, 30).Value = odds
        calculateFuturePL r - 4, betType, stake, odds
    Next
    levelPLRange = pl
End Sub

Private Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
    Dim i As Integer
    For i = 1 To UBound(pl)
        If betType = "BACK" Then
            If i = r Then
                pl(i, 1) = pl(i, 1) + (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) - stake
            End If
        Else
            If i = r Then
                pl(i, 1) = pl(i, 1) - (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) + stake
            End If
        End If
    Next
End Sub

Private Sub initMarket()
    Dim r As Integer
    Set selecIndex = New Collection
    r = 5
    Do
        r = r + 1
    Loop Until Cells(r, 1) = ""
    Set plRange = Range(Cells(5, 28), Cells(r - 1, 28))
    Set levelPLRange = Range(Cells(5, 31), Cells(100, 31))
    levelPLRange = ""
    Set levelPLRange = Range(Cells(5, 31), Cells(r - 1, 31))
End Sub

Private Function getIndex(selecName As String) As Integer
    Dim idx As Integer
    Dim r As Integer
    Dim found As Boolean
    On Error GoTo index_not_found
    idx = selecIndex(selecName)
    getIndex = idx
    Exit Function
index_not_found:
    On Error GoTo 0
    r = 4
    found = False
    Do
        r = r + 1
        If Cells(r, 1).Value = selecName Then
            selecIndex.Add r - 5, selecName
            found = True
        End If
    Loop Until found Or Cells(r, 1).Value = ""
    If found Then getIndex = r - 5 Else getIndex = -1
End Function

Private Sub getIfWinLose(selecName As String, ByRef win As Currency, ByRef lose As Currency)
    Dim idx As Integer
    On Error GoTo index_not_found
    idx = selecIndex(selecName)
    win = ifWin(idx)
    lose = ifLose(idx)
    Exit Sub
index_not_found:
    On Error GoTo 0
    win = 0
    lose = 0
End Sub

What am I doing wrong? Any Ideas?
Thank you

[Captain Sensible]

What's your starting reference on the sheet A1? or some other cell.

Might be an idea to repost your amended code but change the colour on the bits you've amended so it stands out so it'll be easier for people to see where you're going wrong. I'd imagine like you've shifted some starting cells so the pl part isn't reading the correct column and therefore getting a type mismatch as it's reading text instead of numbers or something similar

[andrej]

Yes the starting cell is A1

changes I have made are in red

Private Sub calcGreenUp()
Dim r As Integer, i As Integer
Dim selecName As String
Dim stake As Currency, odds As Currency, win As Currency, lose As Currency, diff As Currency, betType As String
Dim idx As Integer
Dim myBetsRange As Range
r = 2
ReDim ifWin(100)
ReDim ifLose(100)
Set myBetsRange = Worksheets("MyBets").Cells
If currentMarket <> Cells(1, 1) Then initMarket
pl = plRange
currentMarket = Cells(1, 1)
Do
' calculate win and lose positions
If myBetsRange.Cells(r, 5).Value = "F" Then
selecName = myBetsRange.Cells(r, 2).Value
idx = getIndex(selecName)
If idx <> -1 Then
stake = myBetsRange.Cells(r, 3).Value
odds = myBetsRange.Cells(r, 4).Value
If myBetsRange.Cells(r, 6).Value = "B" Then
ifWin(idx) = ifWin(idx) + (stake * (odds - 1))
ifLose(idx) = ifLose(idx) - stake
Else
ifWin(idx) = ifWin(idx) - (stake * (odds - 1))
ifLose(idx) = ifLose(idx) + stake
End If
End If
End If
r = r + 1
Loop Until myBetsRange.Cells(r, 1).Value = ""
' calculate green up stakes
For r = 5 To 50
getIfWinLose Cells(r, 1).Value, win, lose
If win = lose Then
betType = ""
stake = 0
odds = 0
End If
If win > lose Then
betType = "LAY"
diff = win - lose
odds = Cells(r, .Value
If odds <> 0 Then
stake = diff / odds
Else
stake = 0
End If
End If
If win < lose Then
betType = "BACK"
diff = lose - win
odds = Cells(r, 6).Value
If odds <> 0 Then
stake = diff / odds
Else
stake = 0
End If
End If
If stake < 0.01 Then
stake = 0
odds = 0
betType = ""
End If
Cells(r, 28).Value = betType
Cells(r, 29).Value = stake
Cells(r, 30).Value = odds
calculateFuturePL r - 4, betType, stake, odds
Next
levelPLRange = pl
End Sub

Private Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
Dim i As Integer
For i = 1 To UBound(pl)
If betType = "BACK" Then
If i = r Then
pl(i, 1) = pl(i, 1) + (stake * (odds - 1))
Else
pl(i, 1) = pl(i, 1) - stake
End If
Else
If i = r Then
pl(i, 1) = pl(i, 1) - (stake * (odds - 1))
Else
pl(i, 1) = pl(i, 1) + stake
End If
End If
Next
End Sub

Private Sub initMarket()
Dim r As Integer
Set selecIndex = New Collection
r = 5
Do
r = r + 1
Loop Until Cells(r, 1) = ""
Set plRange = Range(Cells(5, 28), Cells(r - 1, 28))
Set levelPLRange = Range(Cells(5, 31), Cells(100, 31))
levelPLRange = ""
Set levelPLRange = Range(Cells(5, 31), Cells(r - 1, 31))
End Sub

Private Function getIndex(selecName As String) As Integer
Dim idx As Integer
Dim r As Integer
Dim found As Boolean
On Error GoTo index_not_found
idx = selecIndex(selecName)
getIndex = idx
Exit Function
index_not_found:
On Error GoTo 0
r = 4
found = False
Do
r = r + 1
If Cells(r, 1).Value = selecName Then
selecIndex.Add r - 5, selecName
found = True
End If
Loop Until found Or Cells(r, 1).Value = ""
If found Then getIndex = r - 5 Else getIndex = -1
End Function

Private Sub getIfWinLose(selecName As String, ByRef win As Currency, ByRef lose As Currency)
Dim idx As Integer
On Error GoTo index_not_found
idx = selecIndex(selecName)
win = ifWin(idx)
lose = ifLose(idx)
Exit Sub
index_not_found:
On Error GoTo 0
win = 0
lose = 0
End Sub

I probably should change some other values but I do not know which.

[andrej]

well I just found out that if I log prices into the original greenup.xls it does nothing, no calculations, no errors, nothing. the one that I modified calculates something ( some strange numbers are populated to the fields before the error)

I must be doing something wrong, but I have no Idea what could it be

[GaryRussell]

well I just found out that if I log prices into the original greenup.xls it does nothing, no calculations, no errors, nothing. the one that I modified calculates something ( some strange numbers are populated to the fields before the error)

I must be doing something wrong, but I have no Idea what could it be

Did you try the original spreadsheet before you amended it? If you've never seen it working then maybe you omitted to log current bets which it needs to do the calculations. Did you tick "Log current bets" when setting up the Excel link? Once you can establish the original spreadsheet works then we can take it from there.

[andrej]

Hi All, now I have found out where the problem is. I have edited the code to write to AB AC AD AE but there is probably a part of the code that reads values from AA. I do not see this int the code but I see that the calculatet P/L is actually a value that I have In AA column. Any Ideas?
Thank you