Hi, I am trying to recreate a spreadsheet based on one I have been given. However, I am trying to ensure I understand the coding properly, I don't want to just cut and paste. I am also making some changes, hence another reason why I don't simply want to do this.
The spreadsheet is based around recording different types of bets. I have made a series of calculators for different types of bets that then paste the information in to a table. So far I have done this by identifying the source, destination etc.
What has completely stumped me, is in the example I have been given, when some of the data is submitted to the table it causes the table to produce 'double rows'. The table is formatted to have one row blue, the next white, but depending on how many entries were are, the data can be split across more than one row but be formatted with a double row of blue, double row of white etc. I am struggling to understand how this has been done. I am attaching the code from the example spreadsheet I have been given:
Sub Back()
Dim NewRow As Integer
NewRow = Worksheets("Calculator").Range("A1").Value + 1
If Worksheets("Calculator").Range("c1").Value <> 0 Then
MsgBox "There are errors. No data has been added!", vbOKOnly, "Error"
Exit Sub
End If
Worksheets("Bets").Cells(NewRow, 2).Value = Worksheets("Calculator").Range("Bet_No.").Value
Worksheets("Bets").Cells(NewRow, 3).Value = Worksheets("Calculator").Range("Date").Value
Worksheets("Bets").Cells(NewRow, 4).Value = Worksheets("Calculator").Range("Event").Value
Worksheets("Bets").Cells(NewRow, 5).Value = Worksheets("Calculator").Range("Bet").Value
Worksheets("Bets").Cells(NewRow, 6).Value = Worksheets("Calculator").Range("Bookmaker").Value
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("Stake").Value
If Worksheets("Calculator").Range("Bet_Type").Value = "Free" Then
Worksheets("Bets").Cells(NewRow, 8).Value = "Yes"
Else
Worksheets("Bets").Cells(NewRow, 8).Value = "No"
End If
If Worksheets("Calculator").Range("Bet_Type").Value = "Free" Then
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Back_Odds").Value - 1
Else
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Back_Odds").Value
End If
Worksheets("Bets").Cells(NewRow, 16).Value = Format(Now(), "mm/dd/yyyy")
End Sub
Sub Lay()
Dim NewRow As Integer
NewRow = Worksheets("Calculator").Range("A1").Value + 1
Worksheets("Bets").Cells(NewRow, 2).Value = Worksheets("Calculator").Range("Bet_No.").Value - 1
Worksheets("Bets").Cells(NewRow, 3).Value = Worksheets("Calculator").Range("Date").Value
Worksheets("Bets").Cells(NewRow, 4).Value = Worksheets("Calculator").Range("Event").Value
Worksheets("Bets").Cells(NewRow, 5).Value = "Not " & Worksheets("Calculator").Range("Bet").Value
Worksheets("Bets").Cells(NewRow, 6).Value = Worksheets("Calculator").Range("Exchange").Value
If Worksheets("Calculator").Range("UnderLay").Value = "Yes" Then
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("LayRiskLiabilityUnderlay").Value
Else
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("Lay_Risk_Liability").Value
End If
Worksheets("Bets").Cells(NewRow, 8).Value = "No"
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Lay_Odds").Value
Worksheets("Bets").Cells(NewRow, 16).Value = Format(Now(), "mm/dd/yyyy")
MsgBox "Matched Bet added"
End Sub
Is anyone able to help explain how this is happening? I would prefer to write the code the the way I have done previously, identifying the source and then destination etc but am struggling with this key bit of formatting within the VBA.
Thank you for any help, I hope have made enough sense with what I have posted.
The spreadsheet is based around recording different types of bets. I have made a series of calculators for different types of bets that then paste the information in to a table. So far I have done this by identifying the source, destination etc.
What has completely stumped me, is in the example I have been given, when some of the data is submitted to the table it causes the table to produce 'double rows'. The table is formatted to have one row blue, the next white, but depending on how many entries were are, the data can be split across more than one row but be formatted with a double row of blue, double row of white etc. I am struggling to understand how this has been done. I am attaching the code from the example spreadsheet I have been given:
Sub Back()
Dim NewRow As Integer
NewRow = Worksheets("Calculator").Range("A1").Value + 1
If Worksheets("Calculator").Range("c1").Value <> 0 Then
MsgBox "There are errors. No data has been added!", vbOKOnly, "Error"
Exit Sub
End If
Worksheets("Bets").Cells(NewRow, 2).Value = Worksheets("Calculator").Range("Bet_No.").Value
Worksheets("Bets").Cells(NewRow, 3).Value = Worksheets("Calculator").Range("Date").Value
Worksheets("Bets").Cells(NewRow, 4).Value = Worksheets("Calculator").Range("Event").Value
Worksheets("Bets").Cells(NewRow, 5).Value = Worksheets("Calculator").Range("Bet").Value
Worksheets("Bets").Cells(NewRow, 6).Value = Worksheets("Calculator").Range("Bookmaker").Value
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("Stake").Value
If Worksheets("Calculator").Range("Bet_Type").Value = "Free" Then
Worksheets("Bets").Cells(NewRow, 8).Value = "Yes"
Else
Worksheets("Bets").Cells(NewRow, 8).Value = "No"
End If
If Worksheets("Calculator").Range("Bet_Type").Value = "Free" Then
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Back_Odds").Value - 1
Else
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Back_Odds").Value
End If
Worksheets("Bets").Cells(NewRow, 16).Value = Format(Now(), "mm/dd/yyyy")
End Sub
Sub Lay()
Dim NewRow As Integer
NewRow = Worksheets("Calculator").Range("A1").Value + 1
Worksheets("Bets").Cells(NewRow, 2).Value = Worksheets("Calculator").Range("Bet_No.").Value - 1
Worksheets("Bets").Cells(NewRow, 3).Value = Worksheets("Calculator").Range("Date").Value
Worksheets("Bets").Cells(NewRow, 4).Value = Worksheets("Calculator").Range("Event").Value
Worksheets("Bets").Cells(NewRow, 5).Value = "Not " & Worksheets("Calculator").Range("Bet").Value
Worksheets("Bets").Cells(NewRow, 6).Value = Worksheets("Calculator").Range("Exchange").Value
If Worksheets("Calculator").Range("UnderLay").Value = "Yes" Then
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("LayRiskLiabilityUnderlay").Value
Else
Worksheets("Bets").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("Lay_Risk_Liability").Value
End If
Worksheets("Bets").Cells(NewRow, 8).Value = "No"
Worksheets("Bets").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("Lay_Odds").Value
Worksheets("Bets").Cells(NewRow, 16).Value = Format(Now(), "mm/dd/yyyy")
MsgBox "Matched Bet added"
End Sub
Is anyone able to help explain how this is happening? I would prefer to write the code the the way I have done previously, identifying the source and then destination etc but am struggling with this key bit of formatting within the VBA.
Thank you for any help, I hope have made enough sense with what I have posted.