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

 

[Andrew Poulsom]

Welcome to MrExcel.

VBA expects commas rather than semicolons as argument separators in a formula, i.e. US style rather than European style.

 

[mad2ogs]

Hello Andrew,

Thank you for your response.

I am sorry to take so long to reply to your answer. I was testing.

Well, i did some adjusting and also changed all semicolons to commas and now the Macro works. Thank you so much for your help.

Another thing happen though, at first i thought the macro worked 100%, because the error 1040 went away, but then i realized a strange thing, excel forces me to "double-click"+"enter" on each cell to make it calculate. Tried everything i know. Automatic calculation, F9, shift+F9, etc...

Eventually, i stopped thinking about it, created my formulas and did a quick "click"+"enter" in all of them manually (for the first column only - about 200 lines)

Thanks a lot, if you (or anybody) have any thoughts on the second issue, i am be glad to hear it. It would help me make an all-in-one working macro to create all formulas i need.

Cheers,
Thiago Morais

 

[Andrew Poulsom]

Make sure that the cells are formatted as other than Text before entering the formula.

 

[mad2ogs]

Tried that and nothing. Actually i tried a couple of cell types.

It is strange though. After the macro runs the cells appear a "#NAME!" error. I than have to do a F2+enter for it to calculate.

Dont know.

Thiago Morais

 

[mad2ogs]

The error that appear on the cell, after running the macro is "#NAME?". Sorry, misspelled.

 

[Andrew Poulsom]

What's your VBA code now?

 

[mad2ogs]

What's your VBA code now?

Here it is!! I´m sure i can improve the code, it´s in a working progress...

Public Sub Criar_Formula()


' Ranges
valores = "Lançamentos!$V:$V"       ' Coluna que já calcula "debito - credito"
ano = "Lançamentos!$D:$D"           ' Coluna ano
mes = "Lançamentos!$C:$C"           ' Coluna mes
contas_geral = "Lançamentos!$E:$E"  ' Coluna Contas
elemento1 = "Lançamentos!$R:$R"     ' Coluna elemento2
elemento2 = "Lançamentos!$S:$S"     ' Coluna elemento3
elemento3 = "Lançamentos!$T:$T"     ' Coluna elemento4


For linha = 15 To 200
    If Format(Plan3.Cells(linha, 1).Value) <> vbNullString And Format(Plan3.Cells(linha, 2).Value) = vbNullString And Format(Plan3.Cells(linha, 3).Value) <> vbNullString Then
        
        variavel1 = Plan3.Cells(linha, 1).Value     ' lista de contas
        Plan3.Cells(linha, 5).Value = "=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 & ") " _
            & ")"
    
    ElseIf Format(Plan3.Cells(linha, 1).Value) <> vbNullString And Format(Plan3.Cells(linha, 2).Value) = vbNullString And Format(Plan3.Cells(linha, 3).Value) = vbNullString Then
        
        variavel1 = Plan3.Cells(linha, 1).Value     ' lista de contas
        Plan3.Cells(linha, 5).Value = "=SOMA(" _
            & "SOMASES(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & "))"
    
    ElseIf Format(Plan3.Cells(linha, 1).Value) <> vbNullString And Format(Plan3.Cells(linha, 2).Value) <> vbNullString And Format(Plan3.Cells(linha, 3).Value) <> vbNullString Then
        
        variavel1 = Plan3.Cells(linha, 1).Value     ' lista de contas
        Plan3.Cells(linha, 5).Value = "=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 & ") + " _
            & "SOMASES(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$R:$R,$B" & linha & ") + " _
            & "SOMASES(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$S:$S,$B" & linha & ") + " _
            & "SOMASES(" & valores & ",Lançamentos!$D:$D,E$9,Lançamentos!$C:$C,E$11,Lançamentos!$E:$E," & variavel1 & ",Lançamentos!$T:$T,$B" & linha & ") " _
            & ")"
    End If
Next linha
MsgBox "Pronto. Formulas Criadas!! Have Fun!!"


End Sub

 

[RoryA]

Try using the English function names (SUM and SUMIFS)

 

[mad2ogs]

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