| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumproduct Help
I have a formula currently in cell G3 working fine, it goes out to another
file and returns a sumproduct: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) There are perhpas 35 other similar formulas in other cells. What I am trying to do is write some VBA so that when the program is run it calculates the sumproduct. But I am struggling with the syntax to get the formula in. This does not work of course: Range("G" & (topRow + cTab)).Formula = "=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2))" Then to make matter harder I want to use variables for the filename. I have defined eslewhere the path and filenames. I am really struggling with the proper syntax converting double quotes to single quotes etc. Anty assistance is appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Sumproduct Help
FlName = "unit 1 Froster.xls"
Range("G" & (topRow + cTab)).Formula = "=SUMPRODUCT(--('X:\unit 01\Equipment\[" & FlName & "]Roster'!$J$3:$J$300=""x""),--(MONTH('X:\unit 01\Equipment\[" & FlName & "]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[" & FlName & "]Roster'!$P$3:$P$300)=$F$2))" "Bigfoot17" wrote in message ... I have a formula currently in cell G3 working fine, it goes out to another file and returns a sumproduct: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) There are perhpas 35 other similar formulas in other cells. What I am trying to do is write some VBA so that when the program is run it calculates the sumproduct. But I am struggling with the syntax to get the formula in. This does not work of course: Range("G" & (topRow + cTab)).Formula = "=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2))" Then to make matter harder I want to use variables for the filename. I have defined eslewhere the path and filenames. I am really struggling with the proper syntax converting double quotes to single quotes etc. Anty assistance is appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Sumproduct Help
I guess I was not clear. Let me try to restate.
This formula works if placed in a cell: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) But Range("G" & (topRow + cTab)) = Application.Evaluate("=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) does not work. I am getting a #Value. "Bigfoot17" wrote: I have a formula currently in cell G3 working fine, it goes out to another file and returns a sumproduct: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) There are perhpas 35 other similar formulas in other cells. What I am trying to do is write some VBA so that when the program is run it calculates the sumproduct. But I am struggling with the syntax to get the formula in. This does not work of course: Range("G" & (topRow + cTab)).Formula = "=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2))" Then to make matter harder I want to use variables for the filename. I have defined eslewhere the path and filenames. I am really struggling with the proper syntax converting double quotes to single quotes etc. Anty assistance is appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Sumproduct Help
I think you'll find that you have to have that sending workbook open to use your
code. But you could plop the formula into the cell and then convert to values. with range("G" & toprow + ctab) .formula = "that long formula" .value = .value end with I don't see that too different than using application.evaluate() and just plopping that value into the cell. Bigfoot17 wrote: I guess I was not clear. Let me try to restate. This formula works if placed in a cell: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) But Range("G" & (topRow + cTab)) = Application.Evaluate("=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) does not work. I am getting a #Value. "Bigfoot17" wrote: I have a formula currently in cell G3 working fine, it goes out to another file and returns a sumproduct: =SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2)) There are perhpas 35 other similar formulas in other cells. What I am trying to do is write some VBA so that when the program is run it calculates the sumproduct. But I am struggling with the syntax to get the formula in. This does not work of course: Range("G" & (topRow + cTab)).Formula = "=SUMPRODUCT(--('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$J$3:$J$300="x"),--(MONTH('X:\unit 01\Equipment\[unit 1 Froster.xls]Roster'!$P$3:$P$300)=1),--(YEAR('X:\unit 01\Equipment\[unit 1 FRoster.xls]Roster'!$P$3:$P$300)=$F$2))" Then to make matter harder I want to use variables for the filename. I have defined eslewhere the path and filenames. I am really struggling with the proper syntax converting double quotes to single quotes etc. Anty assistance is appreciated. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Sumproduct Help
I guess my frustration is "that long formula" does not work in VBA but it
does in a cell. (I do have the sending file open when using the VBA, but it does not need to be open if I use a formula in a cell.) "Dave Peterson" wrote: I think you'll find that you have to have that sending workbook open to use your code. But you could plop the formula into the cell and then convert to values. with range("G" & toprow + ctab) .formula = "that long formula" .value = .value end with I don't see that too different than using application.evaluate() and just plopping that value into the cell. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Sumproduct Help
If the sending workbook is open, then you don't need the drive/path in the
expression in your code. If the sending workbook is closed, then I'd plop the formula into the cell ... but I'm repeating myself. Bigfoot17 wrote: I guess my frustration is "that long formula" does not work in VBA but it does in a cell. (I do have the sending file open when using the VBA, but it does not need to be open if I use a formula in a cell.) "Dave Peterson" wrote: I think you'll find that you have to have that sending workbook open to use your code. But you could plop the formula into the cell and then convert to values. with range("G" & toprow + ctab) .formula = "that long formula" .value = .value end with I don't see that too different than using application.evaluate() and just plopping that value into the cell. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| SUMPRODUCT | Excel Worksheet Functions | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Help with SUMPRODUCT? | Excel Worksheet Functions | |||
Linear Mode
