Excel bets duplicating & refreshing excel tabs

[EyePeaSea]

Hi Peter,

Yup - it is definitely only putting in the 'back' command with odds and stake once. Every time it makes a trade, it records the details into a database. If it fails to write to the DB for any reason, I do indeed output to the immediate window which I check at the end of the day.

Ian

[workpeter]

Yeah sounds like a functional defect.

[burtbrown]

I use this

Private Sub Worksheet_Calculate()


If Range("g1").Value = "In-play" Then
Else
If (Range("V10").Value < 1 And Range("Y10").Value < 1 And Range("O9").Value <> "PLACING" And Range("O9").Value <> "PLACED_KILL_PENDING") Or Range("L9").Value = "CANCEL_ALL" Then
Range("O9").ClearContents
End If
If (Range("V12").Value < 1 And Range("Y12").Value < 1 And Range("O11").Value <> "PLACING" And Range("O11").Value <> "PLACED_KILL_PENDING") Or Range("L11").Value = "CANCEL_ALL" Then
Range("O11").ClearContents
End If
If (Range("V14").Value < 1 And Range("Y14").Value < 1 And Range("O13").Value <> "PLACING" And Range("O13").Value <> "PLACED_KILL_PENDING") Or Range("L13").Value = "CANCEL_ALL" Then
Range("O13").ClearContents
End If
If (Range("V16").Value < 1 And Range("Y16").Value < 1 And Range("O15").Value <> "PLACING" And Range("O15").Value <> "PLACED_KILL_PENDING") Or Range("L15").Value = "CANCEL_ALL" Then
Range("O15").ClearContents
End If
If (Range("V18").Value < 1 And Range("Y18").Value < 1 And Range("O17").Value <> "PLACING" And Range("O17").Value <> "PLACED_KILL_PENDING") Or Range("L17").Value = "CANCEL_ALL" Then
Range("O17").ClearContents
End If
If (Range("V20").Value < 1 And Range("Y20").Value < 1 And Range("O19").Value <> "PLACING" And Range("O19").Value <> "PLACED_KILL_PENDING") Or Range("L19").Value = "CANCEL_ALL" Then
Range("O19").ClearContents
End If
If (Range("V22").Value < 1 And Range("Y22").Value < 1 And Range("O21").Value <> "PLACING" And Range("O21").Value <> "PLACED_KILL_PENDING") Or Range("L21").Value = "CANCEL_ALL" Then
Range("O21").ClearContents
End If
If (Range("V24").Value < 1 And Range("Y24").Value < 1 And Range("O23").Value <> "PLACING" And Range("O23").Value <> "PLACED_KILL_PENDING") Or Range("L23").Value = "CANCEL_ALL" Then
Range("O23").ClearContents
End If
If (Range("V26").Value < 1 And Range("Y26").Value < 1 And Range("O25").Value <> "PLACING" And Range("O25").Value <> "PLACED_KILL_PENDING") Or Range("L25").Value = "CANCEL_ALL" Then
Range("O25").ClearContents
End If
If (Range("V28").Value < 1 And Range("Y28").Value < 1 And Range("O27").Value <> "PLACING" And Range("O27").Value <> "PLACED_KILL_PENDING") Or Range("L27").Value = "CANCEL_ALL" Then
Range("O27").ClearContents
End If
If (Range("V30").Value < 1 And Range("Y30").Value < 1 And Range("O29").Value <> "PLACING" And Range("O29").Value <> "PLACED_KILL_PENDING") Or Range("L29").Value = "CANCEL_ALL" Then
Range("O29").ClearContents
End If
If (Range("V32").Value < 1 And Range("Y32").Value < 1 And Range("O31").Value <> "PLACING" And Range("O31").Value <> "PLACED_KILL_PENDING") Or Range("L31").Value = "CANCEL_ALL" Then
Range("O31").ClearContents
End If
If (Range("V34").Value < 1 And Range("Y34").Value < 1 And Range("O33").Value <> "PLACING" And Range("O33").Value <> "PLACED_KILL_PENDING") Or Range("L33").Value = "CANCEL_ALL" Then
Range("O33").ClearContents
End If
If (Range("V36").Value < 1 And Range("Y36").Value < 1 And Range("O35").Value <> "PLACING" And Range("O35").Value <> "PLACED_KILL_PENDING") Or Range("L35").Value = "CANCEL_ALL" Then
Range("O35").ClearContents
End If
If (Range("V38").Value < 1 And Range("Y38").Value < 1 And Range("O37").Value <> "PLACING" And Range("O37").Value <> "PLACED_KILL_PENDING") Or Range("L37").Value = "CANCEL_ALL" Then
Range("O37").ClearContents
End If
If (Range("V40").Value < 1 And Range("Y40").Value < 1 And Range("O39").Value <> "PLACING" And Range("O39").Value <> "PLACED_KILL_PENDING") Or Range("L39").Value = "CANCEL_ALL" Then
Range("O39").ClearContents
End If
If (Range("V42").Value < 1 And Range("Y42").Value < 1 And Range("O41").Value <> "PLACING" And Range("O41").Value <> "PLACED_KILL_PENDING") Or Range("L41").Value = "CANCEL_ALL" Then
Range("O41").ClearContents
End If
If (Range("V44").Value < 1 And Range("Y44").Value < 1 And Range("O43").Value <> "PLACING" And Range("O43").Value <> "PLACED_KILL_PENDING") Or Range("L43").Value = "CANCEL_ALL" Then
Range("O43").ClearContents
End If
If (Range("V46").Value < 1 And Range("Y46").Value < 1 And Range("O45").Value <> "PLACING" And Range("O45").Value <> "PLACED_KILL_PENDING") Or Range("L45").Value = "CANCEL_ALL" Then
Range("O45").ClearContents
End If
If (Range("V48").Value < 1 And Range("Y48").Value < 1 And Range("O47").Value <> "PLACING" And Range("O47").Value <> "PLACED_KILL_PENDING") Or Range("L47").Value = "CANCEL_ALL" Then
Range("O47").ClearContents
End If
If (Range("V50").Value < 1 And Range("Y50").Value < 1 And Range("O49").Value <> "PLACING" And Range("O49").Value <> "PLACED_KILL_PENDING") Or Range("L49").Value = "CANCEL_ALL" Then
Range("O49").ClearContents
End If
If (Range("V52").Value < 1 And Range("Y52").Value < 1 And Range("O51").Value <> "PLACING" And Range("O51").Value <> "PLACED_KILL_PENDING") Or Range("L51").Value = "CANCEL_ALL" Then
Range("O51").ClearContents
End If

If Range("AH6").Value = 999 Or Range("AH6").Value < 30 Then
Range("O6").ClearContents
End If

End If
End Sub

[workpeter]

Use a loop and you can reduce 90% of that code. Parameterise the rows and increment by 2 each iteration

Also it's unusual coding practice to have nothing in the true part of your conditional statement and all your code in the else part. Just change it from = to <> "in-play" and put it in the true part.

[EyePeaSea]

Hi Burt

Code: Select all

Dim longLoop As Long

If UCase(Range("g1").Value) <> "IN-PLAY" Then

	For longLoop = 9 To 51 Step 2

		If (CLng(Range("V" & CStr(longLoop + 1)).Value) < 1 And _
		   CLng(Range("Y" & CStr(longLoop + 1)).Value) < 1 And _
		   UCase(Range("O" & CStr(longLoop)).Value) <> "PLACING" And _
		   UCase(Range("O" & CStr(longLoop)).Value) <> "PLACED_KILL_PENDING") Or _
		   UCase(Range("L" & CStr(longLoop)).Value) = "CANCEL_ALL" Then
			
		   Range("O" & CStr(longLoop)).ClearContents
			
		End If
		
	Next longLoop
	
	' I assume your AH6 cell is changed by your code?
	If Range("AH6").Value = 999 Or Range("AH6").Value < 30 Then
		Range("O6").ClearContents
	End If

End If

You didn't ask for suggestions, but thought I'd expand on Peter's response a bit - hope it helps.

As Peter said, use a loop. Makes the code smaller and easier to read and also reduces errors. You only have to put the condiditional code in once so less likelihood of a typo

You can use Range with a loop value - Range("Y" & longLoop) - or Range("Y" & cstr(longLoop)). My preference is to use cells(X,Y) where X is the row number and Y is the column number but I've left it as Range, as that's what you've used.

When doing text comparisons, it's good practice (and safer) to convert the spreadsheet text to Upper (or lower) text.

Personally, I'd check if Matched and UnMatched cells were blank ( = "") rather than relying on blank = 0.

To confirm - you want to re-arm the sheet so that once all the bets are matched, it will immediately re-bet using the odds/stake that is already on the sheet?

Ian

[burtbrown]

Hi Ian

Thanks for your reply. The purpose of the code is to submit bets above the price available with killfill and hope to get a match on the drift ( I neet to come up with a way to improve this ) or take sp. I am not a trader although if I had time and math help I could do something I think (in conjunction with the market assesments that my db comes up with). I just try to bet a winner on the horses at the best price. AH6 is a timer to event start, 999 = start time, <30 = change bet persistence to take SP.

BurtBrown