I have a sheet that was given to me that is supposed to download the race ID from gruss, problem is it only works sometimes and i have no idea why.
Can someone have a look and possible make the changes so it will work everytime or if there is a particular way i should be running it.
- Code: Select all
Option Explicit
Dim sRaceDets As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
' ------------------------------------
' PROCESS ALL CHANGES TO THE WORKSHEET
' ------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iReturn As Integer
' Only process whole updates
If Target.Columns.Count <> 16 Then Exit Sub
' Set-up workbook variables
Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("SH_DetectRaces")
Set ws2 = wb1.Sheets("SH_TodaysRaces")
' Stop any further updates until we have completed
Application.EnableEvents = False
' Initialise the bot on the first pass through
If ws1.Range("AB5").Value = "N" Then
Call InitialiseSheet
Else
' Check for a new race
If ws1.Range("A1").Value <> sRaceDets Then
sRaceDets = ws1.Range("A1").Value
ws1.Range("AB7").Value = ws1.Range("AB6").Value
ws1.Range("AB8").Value = "N"
End If
' Check to see if the race should be copied to the log
If ws1.Range("AB8").Value = "N" And ws1.Range("AB7").Value <> 0 And Abs(ws1.Range("AB7").Value - ws1.Range("AB6").Value) >= 1 Then
Call LogRaces
ws1.Range("AB8").Value = "Y"
' Update the spreadsheet to move to the next race in the quick pick list
ws1.Range("Q2").Value = -1
End If
End If
' Enable events as all updates have been completed
Application.EnableEvents = True
End Sub
' --------------
' INITIALISATION
' --------------
Sub InitialiseSheet()
sRaceDets = ""
ws1.Range("AB5").Value = "Y"
ws1.Range("AB7").Value = 0
ws1.Range("AB8").Value = "N"
End Sub
' -----------------------------
' COPY THE RACE NAME TO THE LOG
' -----------------------------
Sub LogRaces()
Dim logRow As Integer
Dim theRow As Integer
' Find out the row number for the next blank line in the log
logRow = ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
' Copy the details to the log
ws2.Range("A" & logRow).Value = sRaceDets
End Sub
All these macros are contained on the one sheet "detectraces"