Assigning formulas using Range().Formula

[jonas91]

Hi,

I am quite new to VBA. I just started with a new macro of which a part is supposed to fill in formulas automatically in 3 loops. These formulas depend on several variables I created earlier. To assign these formules I found I had to use the code Range().Formula. The problem is that it doesn't work in the first 2 loops, but it does in the third. I did the same each time though (only different formules). I hope you can help me out here.

Here some lines of code in a part that doesn't work:

arr1 = celnaam1 & ":" & celnaam2
arr2 = celnaam3 & ":" & celnaam4
functie = "=" & celnaam & "*" & doelf & "-SOMPRODUCT(" & arr1 & ";" & arr2 & ")"
ActiveCell.Offset(1, 0).Select
Range(ActiveCell.Address).Formula = functie

Regards,
Jonas

[AlphaFrog]

What does "doesn't work" exactly mean: error, wrong formula, no formula?

When it "doesn't work", what is in the string variable functie ?

[jonas91]

"Doesn't work" means that I get error message 1004. I can't make anything out of it. I already tried somewhat with adding quotation marks without much effect.

When complete the string functie should contain (using an example where the other variables are filled in): =$A$2*Input!B5-SOMPRODUCT(Input!B5:Input!B10;A3:A8)

It contains SOMPRODUCT, because I am Dutch-speaking and that is also the language in which I use Excel.

I will try to attach the current file as well, if it might help. It also uses the Solver Add-In, if you want to try it.

Regards,
Jonas

[Andy Pope]

try using FormulaLocal

Please Login or Register  to view this content.

[jonas91]

Thaks! That FormulaLocal did the trick.