Thanks Captain - you are always there for me
Prior to seeing this evening your post I had thought the following code was perfect and will cover 100 matches so no need to know the last row.
I am confident the logic is correct, but somewhere my implementation of the coding is faulty and after two hours I have given up trying for the moment.
As there is no Half Time Coupon I am using the Match and Half Time Odds Coupon. The spreadsheet layout, providing you initiate the coupon before the Half Time Odds Market is closed, means that Match Odds are at row 1, Half Time odds at row 11 giving you:
Row 1 Match Odds
Row 11 Half Time Odds
Row 21 Match Odds
Row 31 Half Time Odds
and so on. Hence when R mod 20 = 0 Match Odds Market, and when R mod 20 = 10 Half Time Market.
When I run the code I get a Compile Error - Next Without For. I thought I could enclose all the code inside the For Next Loop but it seems not.
When you have a moment can you amend my code and make it work.
Private Sub Worksheet_Change(ByVal Target As Range)
'Start of Match and End of First Half using Match and Half Time Odds Coupon
For r = 0 To 2000 Step 10
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
'End of First Half using Match and Half Time Odds Coupon
If r Mod 20 = 10 Then
If Cells(2 + r, 5) = "In Play" And Cells(2 + r, 6) = "Closed" Then
If Cells(1 + r, 27) = "" Then Cells(1 + r, 27) = Cells(2 + r, 3)
If Cells(1 + r, 27) <> "" Then Cells(1 + r, 28 ) = DateDiff("s", Cells(1 + r, 27), Cells(2 + r, 3))
End If
If r Mod 20 = 10 Then
If Cells(2 + r, 5) <> "In Play" And Cells(2 + r, 6) <> "Closed" Then
Cells(1 + r, 27) = ""
Cells(1 + r, 28 ) = ""
End If
'Start of Match using Match and Half Time Odds Coupon
If r Mod 20 = 0 Then
If Cells(2 + r, 5) = "In Play" Then
If Cells(1 + r, 27) = "" Then Cells(1 + r, 27) = Cells(2 + r, 3)
If Cells(1 + r, 27) <> "" Then Cells(1 + r, 28 ) = DateDiff("s", Cells(1 + r, 27), Cells(2 + r, 3))
End If
If r Mod 20 = 0 Then
If Cells(2 + r, 5) <> "In Play" And Cells(2 + r, 6) <> "Suspended" Then
Cells(1 + r, 27) = ""
Cells(1 + r, 28 ) = ""
End If
Application.EnableEvents = True
End If
Next r
End Sub
Thanks for the "LastRow" code very helpful and adds greatly to my understanding of VBA. So far all I can do with it is use it as a macro as in:
Sub findlastrow()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
End Sub
As an example, can you show me how I could use that in my included code.
Thanks again for your input and all the very best for the New Year
