3

I was playing around with Excel to try and help out a friend with a problem, and wrote the following macro

Sub test()

Worksheets("Sheet3").Cells(1, 4) = "=SUM(A1:A2)"

End Sub

The idea was to try and figure out if I could create a macro that writes formulas in a specific cell (which we know we could) but also be able to change the range of the formula.

For example, if instead of just 2 values to sum up, I had 4 values, we want the macro to the able to count all the values and then set a range for all the formulas.

The problem here is that when I write the piece of code shown above, for some reason in the cell it appears " =@SUM(A1:A2) ". I have no idea what the "@" symbol is supposed to do nor why it is showing up. As a result though, I get a "name?" error and the function doesn't work. However, if I manually delete the "@" symbol, it works perfectly.

Can anyone explain why the "@" symbol is showing up and how not make it show up ?

Cfun
  • 8,442
  • 4
  • 30
  • 62
Nuno Santos
  • 31
  • 1
  • 2
  • 1
    Use method(s) designed for that: `Worksheets("Sheet3").Cells(1, 4).Formula = "=SUM(A1:A2)"` or its `Local` or `R1C1` versions. [MSDN Range.Formula](https://learn.microsoft.com/en-us/office/vba/api/excel.range.formula) – kolcinx Jun 06 '20 at 15:36
  • 7
    You might succeed using `.Formula2`; to get some ideas have a look at [How to add ynamic array formula](https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula/61138478#61138478) – T.M. Jun 06 '20 at 15:51

1 Answers1

1

This should work :)

As the .Formula is made for excel to recognize that you want to print a formula.

Worksheets("Sheet3").Cells(1, 4).Formula = "=SUM(A1:A2)"
Wizhi
  • 6,424
  • 4
  • 25
  • 47
  • I tried this and the result is the same :/ Edit: Nevermind it's working. I didn't remember that formulas in vba need to be written down in English (i was writing in Portuguese which is the language I use in excel). Thanks for the help :) – Nuno Santos Jun 06 '20 at 15:49
  • Cool. I think in some cases it will actually work without `.Formula`, but there's a high risk that excel treat the output as a text string (the @) and to be sure that you always get formula I would recommend the `.Formula` :). Happy coding – Wizhi Jun 06 '20 at 15:54