Evaluate Sumproduct into an array in VBA

[jacko2401]

I think my previous post went a little haywire with font formatting so created a new one to freshen it up: HiI've done a bit of digging and found that EVALUATE could allow me to put the resulting value into an array. When I've modified the relevant section of code to put the resulting value to an array, the values change to #NAME! when using SUMPRODUCT. Here is the code I have:Dim gameresult as Range 'This is the range of cells where the result of the match is inputtedDim allapps as Range 'This is the range of cells that have the players' names that played in each game Dim PlayerList As Range ' This is the range of cells that have the registered player listDim Winrate() As Array 'This is the array I want to put the result of the sumproduct intoDim i As Integer 'Used to go through each playerSet gameresult = Sheets("Fixtures").Range("F2:F20")Set allapps = Sheets("Fixtures").Range("H2:AU20")Set PlayerList = Sheets("Players").Range("A1:A50")Redim Winrate(1 To PlayerList.Cells.Count)i = 1For each cell in PlayerList Winrate("A" & i) = Evaluate("=SumProduct((allapps=cell.value) * (gameresult=""Win""))") i = i + 1Next The code basically loops through each player name in the Player list worksheet and places the sumproduct result into the array Winrate(). The Sumproduct checks the allapps range to see if the player name exists as well as if the result on each row is a Win. The result is the number of times that player played a game and there was a win.As I am new to Evaluate and this situation, I need to know if I'm getting the basics rights and whether my syntax is correct? Also, is there a better way to do this?I would really appreciate any help someone can provideThanksSteve

 

[pbornemeier]

Re-enter your post with code tags and it should get a better response. (See link in my sig).

 

[jacko2401]

Sorry, I am new to posting code. Thanks for the good tips.

Just to confirm, I have found out the answer to my question after posting. I used a combination of Evaluate and Subproduct. I needed a combination of variables and strings to compose the evalaute formula and put that into the LngResultRate variable.

Re-enter your post with code tags and it should get a better response. (See link in my sig).

LngResultRate = Evaluate("Sumproduct(('" & team & "'!" & RngGameResult.Address & "=""Win"") * ('" & team & "'!" & RngAllAppearances.Address & "=""" & RngIndRegPlayer.Value & """))")