VBA Sumproduct Help

[Bigfoot17]

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.

 

[Bob Umlas]

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

 

[Don Guillett]

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

 

[Bigfoot17]

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.

 

[Dave Peterson]

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.

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.

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.

 

[Bigfoot17]

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]

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.