philbass1978
New Member
- Joined
- Jun 23, 2023
- Messages
- 14
- Office Version
- 365
- 2011
- Platform
- Windows
- MacOS
Hi there,
I am trying to create an excel spreadsheet that will check a train timetable for a given number of values and tell me where certain trains are located.
I have created an excel document which has the following sheets in it (CodeTest, TrainResults, Westbound).
I have written a macro that searches for a set of given times found on the CodeTest sheet in range D9 onwards and looks on the Westbound (only doing one line until I get this right) sheet for the matching time value. When it finds the matching time value which there are multiples over within my data range. It returns the values for the trains headcode (located in row 2 of the Westbound sheet) with its corresponding location (found in column A of the Westbound sheet). This data is then put into the TrainResults sheet for each match found.
The problem I have is that not all data is being returned, in the example I have used I received 20 out of 21 matches (21 matches were confirmed by the excel find functionality)...I also receive a "Run-Time error '13': Type Mismatch" which I think is due to defining my search value as a Date - I have tried changing this to Variable but if I do it doesnt return any values.
I have attached screenshots of the excel file and the Vba code is below in the hope that someone can point me in the right direction as I am stumped.
I am trying to create an excel spreadsheet that will check a train timetable for a given number of values and tell me where certain trains are located.
I have created an excel document which has the following sheets in it (CodeTest, TrainResults, Westbound).
I have written a macro that searches for a set of given times found on the CodeTest sheet in range D9 onwards and looks on the Westbound (only doing one line until I get this right) sheet for the matching time value. When it finds the matching time value which there are multiples over within my data range. It returns the values for the trains headcode (located in row 2 of the Westbound sheet) with its corresponding location (found in column A of the Westbound sheet). This data is then put into the TrainResults sheet for each match found.
The problem I have is that not all data is being returned, in the example I have used I received 20 out of 21 matches (21 matches were confirmed by the excel find functionality)...I also receive a "Run-Time error '13': Type Mismatch" which I think is due to defining my search value as a Date - I have tried changing this to Variable but if I do it doesnt return any values.
I have attached screenshots of the excel file and the Vba code is below in the hope that someone can point me in the right direction as I am stumped.
VBA Code:
Sub GenerateSearchResults()
Dim wb As Workbook
Dim wsCodeTest As Worksheet
Dim wsWestbound As Worksheet
Dim wsResults As Worksheet
Dim searchValues As Range
Dim resultRow As Long
Dim cell As Range
Dim searchValue As Date
Dim foundCell As Range
Dim headCode As String
Dim location As String
' Set the workbook and worksheet objects
Set wb = ThisWorkbook
Set wsCodeTest = wb.Sheets("CodeTest")
Set wsWestbound = wb.Sheets("Westbound")
' Set wsResults = wb.Sheets.Add(After:=wsCodeTest) ' Create a new sheet for results
Set wsResults = wb.Sheets("TrainResults") ' Sheet for results
' Set the range of search values in column D of the "CodeTest" sheet
Set searchValues = wsCodeTest.Range("D9:D200")
' Set the initial row for displaying search results on the "Results" sheet
resultRow = 2
' Loop through each search value
For Each cell In searchValues
searchValue = cell.Value
' Find the first occurrence of the time value on the "Westbound" sheet
Set foundCell = wsWestbound.Range("C5:BD289").Find(What:=CStr(searchValue), LookIn:=xlValues, LookAt:=xlWhole)
' Check if any matching cell is found
If Not foundCell Is Nothing Then
Do
' Get the head code and location from the corresponding row 2 and column A on the "Westbound" sheet
headCode = wsWestbound.Cells(2, foundCell.Column).Value
location = wsWestbound.Cells(foundCell.Row, 1).Value
' Write the search value, head code, and location to the "Results" sheet
wsResults.Cells(resultRow, 1).Value = Format(searchValue, "hh:mm:ss")
wsResults.Cells(resultRow, 2).Value = headCode
wsResults.Cells(resultRow, 3).Value = location
'wsResults.Cells(resultRow, 4).Value = foundCell.Value
'wsResults.Cells(resultRow, 5).Value = foundCell.Address
resultRow = resultRow + 1
' Find the next occurrence of the time value
Set foundCell = wsWestbound.Range("C5:BD289").FindNext(foundCell)
' Check if the loop has completed a full cycle and returned to the first found cell
If foundCell.Address = wsWestbound.Range("C5:BD289").Find(What:=CStr(searchValue), LookIn:=xlValues, LookAt:=xlWhole).Address Then
Exit Do ' Exit the loop if it has completed a full cycle
End If
Loop Until foundCell Is Nothing
Else
' Write "Not Found" if the search value is not found
wsResults.Cells(resultRow, 1).Value = Format(searchValue, "hh:mm:ss")
wsResults.Cells(resultRow, 2).Value = "Not Found"
wsResults.Cells(resultRow, 3).Value = "Not Found"
'wsResults.Cells(resultRow, 4).Value = "Not Found"
'wsResults.Cells(resultRow, 5).Value = "Not Found"
resultRow = resultRow + 1
End If
Next cell
'Rename results sheet
'wsResults.Name = "TrainResults"
' Autofit columns on the "Results" sheet
wsResults.Columns.AutoFit
End Sub