Attempt to put sum formula in cell by using VBA

[CedricMattelaer]

Dear all,

I don't understand why a particular line of code doesn't work.
This is the code:

.Cells(624 + i, 2 + j) = .Cells(624 + i, 2 + j).Formula & "- SUMPRODUCT(" & .Range(.Cells(288 + game, 3 + 14 * j), .Cells(288 + game, 16 + 14 * j)).Address & ";" & .Range(.Cells(315, 3 + 14 * j), .Cells(315, 16 + 14 * j)).Address & ")"

Perhaps this is easyier to read:

Set GameRange = .Range(.Cells(288 + game, 3 + 14 * j), .Cells(288 + game, 16 + 14 * j))
    Set OverflowRange = .Range(.Cells(315, 3 + 14 * j), .Cells(315, 16 + 14 * j))
    
    .Cells(624 + i, 2 + team) = .Cells(624 + i, 2 + team).Formula & "- SUMPRODUCT(" & GameRange.Address & ";" & OverflowRange.Address & ")"

What I'm trying to do is to subtract the current value in the cell by a sumproduct of two matrices somewhere else on the same sheet.
Even if I try "=SUM(" instead of "=SUMPRODUCT", the execution of the code fails with error 1004: application- or object-defined error.

I tried other things too, as replacing the ranges by for example

.Cells(288 + game, 3 + 14 * j).Address & ":" & .Cells(288 + game, 16 + 14 * j).Address

but without success.

Anyh ideas on how to write this correctly?

 

[BigC]

I don't think your 'formula' is producing/returning a 'value' (which -if I understand your problem correctly - should be a mathematical result, so the normal rules of algebra apply), and you may be trying to assign values or strings to range objects when they are expecting something else (hence the 'object-defined error').


Try breaking your code into segments and then test each one till you get them all correct, then join them together. For example:

1. Test that you're defining your GameRange variable correctly (it looks like it is meant to be a range object, so you should be able to Select it via

   GameRange.Select

2. Use

   MsgBox [your formula]

   to test that you're actually returning a meaningful result for each component

HTH

 

[CedricMattelaer]

Thanks for the tip, didn't think of it myself.
Apparently the formula is given in A1 reference style which gives an error if I work in R1C1.
The formula is the following:

=HF351-SUMPRODUCT($C$288:$P$288;$C$315:$P$315)

Is there a way so that it always works, no matter what reference style I use?

Thanks!

 

[CedricMattelaer]

By the way, it still doesn't work, still gives the same error message.
This is strange because when I type the formula in the messagebox in a cell, no problem occurs.

 

[CedricMattelaer]

Hello all,

Unfortunately, I still have no solution to my problem. What's even more strange is that if I for example use the following code and put in the "=" at the front manually, no error is given:

sngFormula = "SOMPRODUCT(" & GameRange.Address & ";" & OverflowRange.Address & ")"
.Cells(624 + i, 214 + team).Formula = sngFormula

However, if I try

.Cells(624 + i, 214 + team).Formula = "=" & sngFormula

the program blocks with error 1004.

Any help would be really appreciated.

 

[CedricMattelaer]

If anyone's interested, I found a very easy way that works.

Instead of writing both adresses separetely, separated with a ";", it is much easyier to define a third range as union of both ranges, and write that in the formula.

So instead of

Cells(1,2) = .Cells(1,2).Formula & "- SUMPRODUCT(" & GameRange.Address & ";" & OverflowRange.Address & ")"

I wrote

Rng3= Union(GameRange, OverflowRange)
 .Cells(1,2) = .Cells(1,2).Formula & "- SUMPRODUCT(" & Rng3.Address & ")"

and that works fine, should have tried it earlier.