Error putting a formula in a cell with vba
 

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.

Error putting a formula in a cell with vba
 

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

Error putting a formula in a cell with vba
 

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!

Error putting a formula in a cell with vba
 

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!

Error putting a formula in a cell with vba
 

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