How to VBA change a cell value (display text) without changing its formula?

Question

I have a problem with this VBA macro.

Sub demoChangeText() Application.Range("B2").Value = "Overidden text" End Sub 

My test is here . To run this macro, open it in Excel, press Ctrl+F8 and select demoChangeText .

As a result, this macro changes the value of cell B2 (the text is displayed to us), but clears its formula. I need to change the B2 value, but also need the formula to stay.

So my question is How to change the displayed text of a cell without changing its formula?

UPDATE

I ask this question because I am trying to solve this problem

Answer 1

I'm not sure if this will help, as it’s a little difficult to say what your basic requirement is, but here it’s all the same:

Several effects on cell display:

• entered value if its constant
• calculation result if its formula
• cell format
• conditional cell format, if any

In the given example, the given formula =ROW()&COLUMN() , which returns the string result 22

You can make this display something else by applying a cell format,
for example, the format 0;0;0;Ov\e\r\ri\d\d\e\nt\ext will display any string value as Overridden text

This can be applied using VBA with

 Range("B2").NumberFormat = "0;0;0;Ov\e\r\ri\d\d\e\nt\ext\s" 

or

 Range("B2").NumberFormat = "0;0;0;""Overridden texts"""