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.

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.

VBA Sumproduct Help
 

This will leave the value of the sumproduct formula

Sub dosumproduct()

'for the formula
Range("j9").Formula = "=SUMPRODUCT((G1:G3=2)*H1:H3)"

'for the value of the formula only
Range("j10") = Application.Evaluate("=SUMPRODUCT((G1:G3=2)*H1:H3) ")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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.

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

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.

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