Hi there!
I try to put a "sum" formula in a cell:

Sub putformula()
Dim suma As String
With Worksheets(1).Activate
Cells(61, 3).Activate
i = 4
j = 27
suma = "=sum(indirect(address(" & i & ";3;4)) :
indirect(address(" & j & ";3;4)))"
ActiveCell.Formula = suma
End With
End Sub

When I run the macro i get the Run time error, 1004 error message,
Application-defined or object-defined error
If I copy the suma string from the locals window (suma =
"=sum(indirect(address(4;3;4)) : indirect(address(27;3;4)))") and paste
it mannualy in the cell, the formula works (it makes the sum(c4:c27).
If i define suma="=sum(c4:c27)" i don't receive any error message, the
macro works. I can't figure it out what i have done wrong. Thank you.

VBA is USA centric.

Try using commas instead of semicolons in your formula.


cristizet wrote:

Hi there!
I try to put a "sum" formula in a cell:

Sub putformula()
Dim suma As String
With Worksheets(1).Activate
Cells(61, 3).Activate
i = 4
j = 27
suma = "=sum(indirect(address(" & i & ";3;4)) :
indirect(address(" & j & ";3;4)))"
ActiveCell.Formula = suma
End With
End Sub

When I run the macro i get the Run time error, 1004 error message,
Application-defined or object-defined error
If I copy the suma string from the locals window (suma =
"=sum(indirect(address(4;3;4)) : indirect(address(27;3;4)))") and paste
it mannualy in the cell, the formula works (it makes the sum(c4:c27).
If i define suma="=sum(c4:c27)" i don't receive any error message, the
macro works. I can't figure it out what i have done wrong. Thank you.

--

Dave Peterson

On Jan 23, 2:40 am, Dave Peterson wrote:
VBAis USA centric.

Try using commas instead of semicolons in yourformula.

Thanks for your advice, but it doesnt work.
I tried with others formulas, and the macro is ok! It seems that only
"sum" formula is "object defined error".
Need your help, thanks in advance!

try double quotes in your quotes in the formula.........

"=sum(indirect(address("" & i & "";3;4))

?
susan

On Mar 8, 2:56 am, "cristizet" wrote:
On Jan 23, 2:40 am, Dave Peterson wrote: VBAis USA centric.

Try using commas instead of semicolons in yourformula.

Thanks for your advice, but it doesnt work.
I tried with others formulas, and the macro is ok! It seems that only
"sum" formula is "object defined error".
Need your help, thanks in advance!

the "suma" variable is a string. If I double the quote the string that
define the formula is distorted. Thanks 4 ur advice, but is no good.
I renamed the string variable with "formulastring"
This how my macro looks like now:

Sub putformula()
Dim formulastring As String
With Worksheets(1).Activate
Cells(61, 3).Activate
i = 4
j = 27
formulastring = "=sum(indirect(address(" & i & ";3;4)) :
indirect(address(" & j & ";3;4)))"
ActiveCell.Formula = formulastring
End With
End Sub

I stepped into the macro ... when it got to the line: formulastring =
"=sum(indirect(...., the locals window show the result :
formulastring : "=sum(indirect(address(4;3;4)) :
indirect(address(27;3;4)))"
The string is absolutely perfect! If i copy the string from the locals
window and paste it into the cell, the formula works!!!
If I change the formula, so it doesn't contain sum or indirect or
address, the macro works.
Thanks again.




On Mar 8, 11:06 pm, "Susan" wrote:
try double quotes in your quotes in the formula.........

"=sum(indirect(address("" & i & "";3;4))

?
susan