Evaluate function giving error 2029 using Excel VBA

Evaluate is throwing error 2029 (#NAME).

The project is using Census procedures to allocate seats in the House of Representatives for any given number of house members (currently 435). There are a number of proposals to expand the size of the House. I'm investigating budget and economic implications.

My workbook has a sheet named PVC. I'm trying to find the maximum value in column E (number of seats allocated to a given state) for a value in column C (two-letter state abbreviations).

I have removed quite a few lines.
I have experimented with a user-defined function MaxIf().

Sub CountSeatsEval()

  Dim lNoSeats, lG2, lastrow, lStateRow, lStateSeats, lStateNo As Long
  Dim sFileName, sPathName, sFunction, sSearchValue, sSearchState As String
  Dim sStateAbbr, vStateSeats As Variant
  Dim wsSource, wsTarget As Worksheet
  Dim rMaxRange, rSearchValue, rSearchState As Range
  Dim rLookup1 As Range
  Set wsSource = ThisWorkbook.Worksheets("PVC")
  'Following line is to make life easy temporarily
  Set wsTarget = ThisWorkbook.Worksheets("PVC")
  lNoSeats = wsSource.Range("G2").Value
...
  'Copy and paste G2 to replace formula with value
  wsTarget.Range("G2").Copy
  wsTarget.Range("G2").PasteSpecial (xlPasteValues)
  lastrow = wsTarget.Cells(Rows.Count, 6).End(xlUp).Row
...
  sSearchValue = "'PVC'!E2:$E$" & lastrow
  sSearchState = "'PVC'!$C$6"
...
   sStateAbbr = "CA"
   lStateRow = 6
   vStateSeats = Evaluate("IF((MAXIFS(sSearchValue, sSearchState, sSearchState))>0,(MAXIFS(sSearchValue, sSearchState, sSearchState)),1)")
End Sub

sSearchValue and sSearchState are VBA local variables:

Dim sFileName, sPathName, sFunction, sSearchValue, sSearchState As String

Note that this statement declares sSearchState as a String, and then leaves all other 4 variables without a declared type, making them implicit Variant variables (see VariableTypeNotDeclared Rubberduck inspection details).

Being local VBA variables, they live in the VBA runtime context, and Excel doesn't have the slightest idea about their existence - so you get a #NAME? error:

In Excel you get a #NAME? error whenever you try to evaluate a formula that contains a name that Excel cannot resolve in the current context.

So you need to have VBA evaluate the variables' values before you send the resulting expression over to Excel's calculation engine; you can do this by splitting up the string and using the concatenation operator (&):

vStateSeats = wsTarget.Evaluate("IF((MAXIFS(" & sSearchValue & "," & sSearchState & "," & sSearchState & "))>0,(MAXIFS(" & sSearchValue & "," & sSearchState & "," & sSearchState & ")),1)")

Unqualified, Evaluate will invoke [_Global].Evaluate, which is essentially Application.Evaluate, which may or may not produce the desired results - by qualifying it with a specific Worksheet object, the formula evaluates in the context of that worksheet.