Variable for Worksheet Name & Syntax for use in formula

[Mike]

I need to assign a variable for the Worksheet Name property each time
this module is run and use that variable in a SUMPRODUCT formula. The
worksheet Name will be different each time the module is run.

Below are four lines from my code module. I suspect my problem is poor
syntax in the formula code.

Dim FPsh As String

FPsh = ActiveSheet.Name

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)" & ")"

Runtime error occurs at the formula line (above). Help appreciated in
advance...Mike

 

[Bob Phillips]

Not enough closing brackets


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)" & ")))"

but you don't need all those brackets anyway

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))"

I'm not convinced that you need the doiuble-unary before G4, but without
seeing your data, I left it.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)