Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable for Worksheet Name & Syntax for use in formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable for Worksheet Name & Syntax for use in formula
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) "Mike" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Syntax for using a variable in a worksheet function | Excel Discussion (Misc queries) | |||
syntax when using a variable in a formula | Excel Programming | |||
syntax for variable | Excel Programming | |||
For each syntax using a worksheet array variable | Excel Programming | |||
Syntax using variable in cell Formula | Excel Programming |