You should use Dim FPsh as worksheet to declare, and SET FPsh = whatever it is.
As far as the quotation marks are concerned - If you want to use quotation
marks in a formula, then in VBA you must enclose it in quotation marks. The
formula
=IF(B1="","",B1), in VBA looks like
Range("E1").Formula = "=IF(B1="""","""",B1)".
The formula ='[TestOne.xls]Sheet1'!$A$1 when using a variable, can be written
Range("E1").formula = "='[" & wbkOne &"]Sheet1'!$A$1"
In this example, a single quotation mark is used to enclose a file
reference. Otherwise, a single quotation mark is used to mark a comment

"Mike" wrote:

Having trouble using variable in SUMPRODUCT formula...any help/comments
appreciated. I don't know how to use quotation marks (double or
single) correctly.


Dim FPsh = ActiveSheet.Name 'assign whsht name as variable FPsh
Worksheets("2005").Select

Range("E6").Formula = "=SUMPRODUCT((" & FPsh & "!$E$1:$E$1000=$C6)*" &
"(" & FPsh & "!$B$1:$B$1000(--(""2004/12/31"")))*" & "((" & FPsh &
"!$B$1:$B$1000<(--(G$4))))*" & "(((" & FPsh & "!$J$1:$J$1000)" & ")"

Many thanks...Mike