How to create a list of home fixtures

[Oakey]

I am looking for the best way to create a list of home fixtures for a specific date (this will help me to see book a pitch at certain times), which will show me the team that's playing at home and the kick off time. The home team will always be a 'Weedon' Team

I have 6 age groups and have all their fixtures on separate tabs. I know i could put all the information on the same sheet and do a data sort, but im looking to get them to link in from the Raw data i get from the website.

A snip of the information i get is below

Under 8's

[TABLE="width: 701"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Home Team[/TD]
[TD][/TD]
[TD]Away Team[/TD]
[TD]Time[/TD]
[TD]Home/Away[/TD]
[/TR]
[TR]
[TD]10th Sept[/TD]
[TD]Pitsford Pumas[/TD]
[TD]-[/TD]
[TD]Weedon under 8 Yellow[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]17th Sept[/TD]
[TD]Weedon under 8 Yellow[/TD]
[TD]-[/TD]
[TD]Weedon Navy[/TD]
[TD]10am[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]24th Sept[/TD]
[TD]Weedon under 8 Yellow[/TD]
[TD]-[/TD]
[TD]Welland Valley Vipers[/TD]
[TD]9am[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]1st Oct[/TD]
[TD]303 Polish[/TD]
[TD]-[/TD]
[TD]Weedon under 8 Yellow[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]15th Oct[/TD]
[TD]Weedon under 8 Yellow[/TD]
[TD]-[/TD]
[TD]Long Buckby[/TD]
[TD]9.30am[/TD]
[TD]Home


[/TD]
[/TR]
</tbody>[/TABLE]
Under 9's

[TABLE="width: 660"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Home team[/TD]
[TD][/TD]
[TD]Away Team[/TD]
[TD]Time[/TD]
[TD]Venue[/TD]
[/TR]
[TR]
[TD]10th Sept[/TD]
[TD]Brixworth Panthers[/TD]
[TD]-[/TD]
[TD]Weedon Under 9 Navy[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]14th Sept[/TD]
[TD]Roade[/TD]
[TD]-[/TD]
[TD]Weedon Under 9 Navy[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]24th Sept[/TD]
[TD]Weedon Under 9 Navy[/TD]
[TD]-[/TD]
[TD]Delapre Dragons[/TD]
[TD]11am[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]1st Oct[/TD]
[TD]Daventry Town Vikings[/TD]
[TD]-[/TD]
[TD]Weedon Under 9 Navy[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]15th Oct[/TD]
[TD]Weedon Under 9 Navy[/TD]
[TD]-[/TD]
[TD]Drayton Grange[/TD]
[TD]11am[/TD]
[TD]Home[/TD]
[/TR]
</tbody>[/TABLE]

Under 10's

[TABLE="width: 726"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Home Team[/TD]
[TD][/TD]
[TD]Away Team[/TD]
[TD]Time[/TD]
[TD]Venue[/TD]
[/TR]
[TR]
[TD]10th Sept[/TD]
[TD]Weedon Under 10[/TD]
[TD]-[/TD]
[TD]Moulton White[/TD]
[TD]11am[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]17th Sept[/TD]
[TD]Weedon Under 10[/TD]
[TD]-[/TD]
[TD]West Haddon[/TD]
[TD]11am[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]24th Sept[/TD]
[TD]Rushden & Higham[/TD]
[TD]-[/TD]
[TD]Weedon Under 10[/TD]
[TD]10.30am[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]1st Oct[/TD]
[TD]Weedon Under 10[/TD]
[TD]-[/TD]
[TD]Towcester Town Tigers[/TD]
[TD]11am[/TD]
[TD]Home[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for any help

Stuart

 

[MrTeeny]

easiest way would be to simply check the column holding the Home value and then add an additional check to also match the date. I've no idea of how you've formatted your data but it pastes into A:F so if we assume that and your dates are strings not actual date format. Just enter the date in the same format into H1 then run the macro obviously you can always tweak it to enter by text boxes , calendars etc

Sub GetMatches()    
    Dim i As Long, j As Long: j = 2
    Dim MatchDate As String
    Dim LRow As Long
    MatchDate = Range("H1").Value

    
    LRow = Cells(Rows.Count, "F").End(xlUp).Row
    For i = 1 To LRow
    
    If Range("A" & i).Value = MatchDate And Range("F" & i).Value = "Home" Then
    Range("H" & j & ":M" & j).Value = Range("A" & i & ":F" & i).Value
    j = j + 1
    End If
    
  
    Next i


End Sub

 

[Oakey]

Thanks mate, they are in columns A:F.
Not quite sure how your macro works, im trying to understand it step by step

 

[MrTeeny]

All it's doing is setting some variables at the start (Dim) Takes the value in H1 and sets it as MatchDate, the date you want to search.

Lrow just finds the last row in column F so we can loop thru the data one at a time For i = 1 To LRow ,, when i =1 Range("A" & i).Value would be Range("A1").Value ,Range("F" & i).Value would be Range("F1").Value ,when i =2 Range("A" & i).Value would be Range("A2").Value ,Range("F" & i).Value would be Range("F2").Value etc

It now just loops thru our filled data looking for column F to equal Home and column A to equal the Matchdate you entered in H1. If it finds a match Then it copies the data from A:F into the range H:J staring at H2 as we earlier set j to equal 2, if we didn't set j to be 2 it would overwrite our data in H1. it then add 1 to J so if we find another match j now equals 3 and we write to H3:J3 so not overwriting data we've already copied.