Error putting a formula in a cell with vba

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cristizet - 23 Jan 2007 00:12 GMT

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.

Reply to this Message

Dave Peterson - 23 Jan 2007 00:40 GMT

VBA is USA centric.

Try using commas instead of semicolons in your formula.

> Hi there!
> I try to put a "sum" formula in a cell:
[quoted text clipped - 18 lines]
> 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.

Signature

Dave Peterson

Reply to this Message

cristizet - 08 Mar 2007 07:56 GMT

> 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!

Reply to this Message

Susan - 08 Mar 2007 20:06 GMT

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

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

?
susan

> On Jan 23, 2:40 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:> VBAis USA centric.
>
[quoted text clipped - 4 lines]
> "sum" formula is "object defined error".
> Need your help, thanks in advance!

Reply to this Message

cristizet - 22 Apr 2007 14:11 GMT

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.

> try double quotes in your quotes in the formula.........
>
> "=sum(indirect(address("" & i & "";3;4))
>
> ?
> susan

Reply to this Message