How to link strings in a formula in VBA?

[svai]

Hi,

I've run into a problem I can't come around:

I want to give a number of cells a formula:

=DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5)

(DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5
changes for each target cell)

I do it in VBA like this (don't mind the i:s and j:s):

Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" &
Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address &
")"

The connection between the target cell and the reference cells is a bit
messy, hence the i:s and j:s.

However, this generate a 1004 error, which I trace to the "=DSUMMA"-string;
Excel refuses to accept the formula. Showing the whole string
("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I
want to put in the cells (which works if I do).

How do I overcome this?

Thanks
/Stefan

 

[Bob Phillips]

Stefan,

In VBA, try using DSUM and let Excel convert it to the Swedish equivalent.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

You need to see why Excel is complaining. Instead of something like:
rmg.Formula=your_formula
first try:
rng.Value= Chr(39) & your_formula

Then go to the worksheet and try to remove the single quote to view the
problem

 

[svai]

Hi Bob, I tried that, but it gives the same error: "Application-defined or
object-defined error" /Stefan

 

[svai]

Well, here's the problem: I place the single quote in front of my formula in
vba, and when I remove the single quote in Excel it results in a working
formula... It then works exactly as intended. But without the "Chr(39) &"
there's an error occuring... /Stefan

 

[Bob Phillips]

Stefan,

I know that my advice is correct, but I am not sure what happens with
analysis toolpak functions (probably doesn't work), if the semi-colons use
the same principle.

Please try these options

Cells(114 + 15 * i + j, 3).Value = _
"=DSUM(Listan!$D:$G,""Förbrukning""," & _
Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 *
i)).Address & ")"


But thinking about it, I think it is the analysis toolpak problem.

How many cells are being compared against in Range(Cells(2 + 2 * j, 34 + 2 *
i), Cells(3 + 2 * j, 35 + 2 * i))? Maybe need a different formula.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tom Ogilvy]

But thinking about it, I think it is the analysis toolpak problem.
I am missing the connection. I didn't see any functions listed that are in
the analysist toolpak????

Changing Value to the more Explicit Formula might help

Cells(114 + 15 * i + j, 3).Formula = _
"=DSUM(Listan!$D:$G,""Förbrukning""," & _
Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 *
i)).Address & ")"