[excel] how to do "enter" in a cell or how to calculate the cell with VBA

Question

Good evening all,

First of all english is not my mother langage, so I apologize,

I have a question about a VBA I made a macro that works but not in the way I want.

I have a sheet that is the sum of a yearly planning updated monthly. In this sheet I have some formula that are for exemple =xxcolumn of the table in sheet xx but well coded. The formula is in text so I can tip it without a message error.

My macro is this one :

For Each c In Range("D5:E7")

'c.Select 'I tried with or without and it is not working...

c.NumberFormat = "0.00"

c.Application.SendKeys "{F2}", True

c.Application.SendKeys "~", True

Next c

If I apply number format it will work but I have to press enter in the cell. If I run the macro it will work for the first column if I selected the upper cell before clicking my macro because it will make "my range" times enter . If I do the step by step in macro it will make enter in the macro ...

I am a bit lost ...

I tried to use .calculate but it is not working well.

Does anyone know what to do ?

Thanks for reading

Answer 1

What you are trying to do is not possible, you cannot switch to edit mode with VBA.

You can write a formula into a cell using e.g. the Formula property or a value using the Value property. See code below.

Usually the calculation mode is set to Automatic, so the result should be in the cell after the macro is run.

Andreas.

Sub Test()
  Range("A1").Value = 1
  Range("A2") = 2
  Range("A3").Formula = "=SUM(A1:A2)"
End Sub