Using VBA to add formula to cell

[mad2ogs]

Hello to all,

I´m stuck with a problem (MS excel pro plus 2013).

This works ok, it just adds the string to the cell:

[SIZE=2]Plan2.Cells(20, 7).Value = "SUM(" _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$R:$R;$C20) + " _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$S:$S;$C20) + " _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$T:$T;$C20))"[/SIZE]

contas = Plan2.Cells(20, 1).Value
This cell contains a sequence of numbers exactly like this:
{4111001;4111002;4111004;4112001}

But what i want is for it to execute. Like this:

[SIZE=2]Plan2.Cells(20, 7).Value = "[COLOR=#ff0000]=[/COLOR]SUM(" _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$R:$R;$C20) + " _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$S:$S;$C20) + " _[/SIZE]
[SIZE=2]    & "[/SIZE]sumifs[SIZE=2](Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;" & contas & ";Lançamentos!$T:$T;$C20))"[/SIZE]

But when i execute the macro, it gives me a Error 1004.

I tried using the Evaluate function. But it does not work. It executes but gives a "#VALUE!" error.
I also tried not using the "contas" variable and i have same error.

Plan2.Cells(20, 7).Value = "SUM(" _
   & "sumifs(Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;{4111001;4111002;4111004;4112001};Lançamentos!$R:$R;$C20) + " _
   & "sumifs(Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;{4111001;4111002;4111004;4112001};Lançamentos!$S:$S;$C20) + " _
   & "sumifs(Lançamentos!$V:$V;Lançamentos!$D:$D;G$9;Lançamentos!$C:$C;G$11;Lançamentos!$E:$E;{4111001;4111002;4111004;4112001};Lançamentos!$T:$T;$C20))"

Strange thing is that if i copy and paste this exact formula on excel, it works. What i need is to create a sequence of formulas where the "{4111001;4111002;4111004;4112001}" part changes. And i need to pull this information from another cell into the formula.

Plan2.Cells(20, 5).Value = Evaluate(Plan2.Cells(20, 7))

Not sure how to overcome this. Any help will be welcome.

Cheers
Thiago Morais

 

[RoryA]

Does it help if you use the .Formula property rather than the .Value property?

 

[mad2ogs]

Does it help if you use the .Formula property rather than the .Value property?

No. Same things. The text goes to the cell and appears a "#NAME?" error. I click on the cell, press F2+enter for the formula to calculate.

Very strange this.... fortunately i discovered the "F2+enter" workaround, or else i would be still on step 1 of my problem.

Thiago Morais

 

[Andrew Poulsom]

If you record a macro while entering the formula what VBA code do you get?

 

[Marcelo Branco]

Well, my office is portuguese (brazil). I´m not sure if the function names should work in english, but in my case they don´t. As you might already noticed my SOMA is SUM and SOMASES is SUMIFS. I manually changed theses names for my first post (i thought it would help whoever read the post to figure out how to help me if the functions were in english).

Anyway, my code has always used funcions in portuguese. Do you thing this might be the problem here?

Thank you for helping...

Thiago Morais

Hi Thiago

An example

This doesn't work (#NAME error)

Plan2.Range("C2")[COLOR=#ff0000].Value[/COLOR]= "=SOMA(A2:B2)"

This works

Plan2.Range("C2").[COLOR=#0000ff]FormulaLocal[/COLOR]= "=SOMA(A2:B2)"

Or in English

Plan2.Range("C2").Formula= "=SUM(A2:B2)"

M.

 

[Marcelo Branco]

Another example

'FormulaLocal in Portuguese - Works
'Observe the use of semicolon ; as argument separator
Plan2.Range("E2").FormulaLocal = "=PROCV(A2;A:B;2;0)"
    
'Formula in English - also works
'Observe the use of comma , as argument separator
Plan2.Range("F2").Formula = "=VLOOKUP(A2,A:B,2,0)"

M.

 

[mad2ogs]

Ok, now i get it. Did some testing here with what Marcelo said. (valeu!!)

This works fine, and it does not give me the "#NAME?" error.

         Plan3.Cells(linha, 5).FormulaLocal = "=SOMA(" _
            & "SOMASES(" & valores & ";Lançamentos!$D:$D;E$9;Lançamentos!$C:$C;E$11;Lançamentos!$E:$E;" & variavel1 & ";Lançamentos!$R:$R;$C" & linha & ") + " _
            & "SOMASES(" & valores & ";Lançamentos!$D:$D;E$9;Lançamentos!$C:$C;E$11;Lançamentos!$E:$E;" & variavel1 & ";Lançamentos!$S:$S;$C" & linha & ") + " _
            & "SOMASES(" & valores & ";Lançamentos!$D:$D;E$9;Lançamentos!$C:$C;E$11;Lançamentos!$E:$E;" & variavel1 & ";Lançamentos!$T:$T;$C" & linha & ") " _
            & ")"

But i had already coded a lot using commas (i know i could change it all to semicolon, but...), so i will stick with this one, that also works:

        Plan3.Cells(linha, 5).Formula = "=SUM(" _
            & "SUMIFS(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$R:$R,$C" & linha & ") + " _
            & "SUMIFS(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$S:$S,$C" & linha & ") + " _
            & "SUMIFS(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$T:$T,$C" & linha & ") " _
            & ")"

Thank you all for your help. Really!!! Saved me quite a lot of time. Before posting here i was creating formula one by one manually.

I must apologize to RoryA. When he said to test with english name functions, i went directly to excel and got erros trying to create formulas into the cells. I really did not try changing them in the VBA code. (Sorry mate)

Cheers
Thiago Morais

 

[Marcelo Branco]

Thiago

Glad to help . Thanks for the feedback (Valeu!!!)

M.