Applying Sumproduct Formula to Cell via VBA Failing

[Ark68]

I am trying to find a VBA solution to using the countif function with a closed workbook. With reference to this post, I have attempted to to do this. My attempt is failing, with a #NAME error in the destination cell.
Here is the line of code I'm using to apply the formular to the cell.

.Cells(1, 30) = "=SUMPRODUCT(('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$E2000=str_func)+0)"

Any suggestions on where I have gone wrong?

 

[Fluff]

Maybe

.Cells(1, 30) = "=SUMPRODUCT(('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$E2000=" & str_func & ")+0)"

 

[Ark68]

Wow! You're fast Fluff!! I appreciate that very much.
I replaced by error ridden line with your's and I'm finding I'm not getting an "Application-defined or object-defined error."

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Ark68]

Yes, it is ...

         With Worksheets("thold") 'ws_thold
            .Cells(1, 26) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$E2000),5,FALSE)" 'function
            .Cells(1, 27) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$H2000),8,FALSE)" 'division
            .Cells(1, 28) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$H2000),6,FALSE)" 'league
            .Cells(1, 29) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$H2000),7,FALSE)" 'calibre
            str_func = Worksheets("thold").Cells(1, 26) & Worksheets("thold").Cells(1, 28) & Worksheets("thold").Cells(1, 29) & Worksheets("thold").Cells(1, 27) 'functionleaguecalibredivision
            .Cells(1, 30) = "=SUMPRODUCT(('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$E2000=" & str_func & ")+0)"
            If .Cells(1, 30).Value > 0 Then
                .Cells(1, 31) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$M2000),2,FALSE)" 'base
                .Cells(1, 32) = "=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$M2000),9,FALSE)" 'pitching
            End If
        End With

 

[Fluff]

You marked my post as the solution & said

I'm finding I'm not getting an "Application-defined or object-defined error."

So I asumed that everything was working. Is that the case?

 

[Ark68]

No ... your first solution resolved the original problem, but on testing it, I have a new problem ... the application-defined or object -defined error.

"Not" was supposed to be "Now" ...

 

[Fluff]

On which line?

 

[Ark68]

The same one ...

 .Cells(1, 30) = "=SUMPRODUCT(('D:\WSOP 2020\[permit_data.xlsx]Activity_Default'!$E2:$E2000=" & str_func & ")+0)"

 

[Fluff]

What is the exact value of str_func?