Replaced your textbox with a combobox.
E11 =COUNTIF(Player1,testname)
E12 =SUMPRODUCT(--(Player1=testname),--(Score1>Score2))
E13 =E11-E12-E14
E14 =SUMPRODUCT(--(Player1=testname),--(Score1=Score2))
Private Sub ComboBox1_Change()
Dim FirstRow As Variant, _
LastRow As Variant, _
GameCount As Long, _
DataSheet As Worksheet, _
QuerySht As Worksheet, _
Gamer As Range
Application.ScreenUpdating = False
Set DataSheet = Sheets("MatchData")
Set QuerySht = Sheets("query")
Set Gamer = QuerySht.Range("E10")
GameCount = QuerySht.Cells(Rows.Count, "B").End(xlUp).Row
QuerySht.Range("B17:J17").Resize(RowSize:=GameCount).Value = ""
With DataSheet
Call sortPlayer1
With .Range("player1")
Set LastRow = .Find(Gamer, SearchDirection:=xlPrevious, lookat:=xlWhole)
Set FirstRow = .FindNext(LastRow).Offset(columnOffset:=-3)
Set LastRow = LastRow.Offset(columnOffset:=5)
End With 'player list
.Range(FirstRow.Address(0, 0), LastRow.Address(0, 0)).Copy _
Destination:=QuerySht.Range("B17:J17").Resize(RowSize:=(LastRow.Row - FirstRow.Row + 1), columnsize:=9)
Call sortPlayer2
With .Range("player2")
Set LastRow = .Find(Gamer, SearchDirection:=xlPrevious, lookat:=xlWhole)
Set FirstRow = .FindNext(LastRow)
Set FirstRow = FirstRow.Offset(columnOffset:=-6)
Set LastRow = LastRow.Offset(columnOffset:=2)
End With 'player list
GameCount = QuerySht.Cells(Rows.Count, "B").End(xlUp).Row
.Range(FirstRow.Address(0, 0), LastRow.Address(0, 0)).Resize(columnsize:=9).Copy _
Destination:=QuerySht.Range("B1").Offset(rowoffset:=GameCount).Resize(RowSize:=(LastRow.Row - FirstRow.Row + 1), columnsize:=9)
End With 'datasheet
Application.ScreenUpdating = True
End Sub
Sub sortPlayer1()
ActiveWorkbook.Worksheets("MatchData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MatchData").Sort.SortFields.Add _
Key:=Range("e5:e7774")
With ActiveWorkbook.Worksheets("MatchData").Sort
.SetRange Range(Worksheets("MatchData").Range("matchdata").Address(0, 0))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub sortPlayer2()
ActiveWorkbook.Worksheets("MatchData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MatchData").Sort.SortFields.Add Key:=Range( _
"H5:H7774"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("MatchData").Sort
.SetRange Range(Worksheets("MatchData").Range("matchdata").Address(0, 0))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Bookmarks