Hi
I have a worksheet called Fixtures that includes basic fixture details of matches (columns A to E), the result (Column F) and players that played in that match (Columns H, J, L, N...etc) including if they scored (Columns I, K, M, O...etc).
The Result Column F contains Win, Lose, Draw.
The player columns H, J, L, N contain player names
The goals scored columns I, K, M, O contain integer numbers of goals scored
I have another worksheet called stats that holds different stats on appearances and goals. I use a multi dimensional array to complete the stats table that works well for all stats I have so far. I would like to put an extra column in the array that shows win rate (show the % of games that the player played where there was a win). I have tried Application.WorksheetFunction.Sumproduct but I cannot seem to get them to work.
The code I have for Sumproduct is:
Dim gameresult as Range, allapps as Range, players As Range
Dim i As Integer
gameresult = Sheets("Fixtures").Range("F2:F20")
allapps = Sheets("Fixtures").Range("H2:O20")
Dim PlayersList(1 to 20, 1 to 5)
i = 1
For i = 1 to 20
PlayersList(i, 5) = Application.WorksheetFunction.SumProduct(--(gameresult = "Win"), --(allapps = player.Value))
i = i + 1
Next i
However, I get a runtime error 13: Type Mismatch. Can anyone tell me where I am going wrong with SumProduct or whether I can use Countifs with teh data types and the ranges I define in VB.
I have a worksheet called Fixtures that includes basic fixture details of matches (columns A to E), the result (Column F) and players that played in that match (Columns H, J, L, N...etc) including if they scored (Columns I, K, M, O...etc).
The Result Column F contains Win, Lose, Draw.
The player columns H, J, L, N contain player names
The goals scored columns I, K, M, O contain integer numbers of goals scored
I have another worksheet called stats that holds different stats on appearances and goals. I use a multi dimensional array to complete the stats table that works well for all stats I have so far. I would like to put an extra column in the array that shows win rate (show the % of games that the player played where there was a win). I have tried Application.WorksheetFunction.Sumproduct but I cannot seem to get them to work.
The code I have for Sumproduct is:
Dim gameresult as Range, allapps as Range, players As Range
Dim i As Integer
gameresult = Sheets("Fixtures").Range("F2:F20")
allapps = Sheets("Fixtures").Range("H2:O20")
Dim PlayersList(1 to 20, 1 to 5)
i = 1
For i = 1 to 20
PlayersList(i, 5) = Application.WorksheetFunction.SumProduct(--(gameresult = "Win"), --(allapps = player.Value))
i = i + 1
Next i
However, I get a runtime error 13: Type Mismatch. Can anyone tell me where I am going wrong with SumProduct or whether I can use Countifs with teh data types and the ranges I define in VB.