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

Baptiste 25 Reputation points
2024-04-22T13:35:37.93+00:00

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

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
3,834 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,477 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,509 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 80 Reputation points
    2024-04-22T15:07:45.94+00:00

    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