Formula is not active until double clicked

[MarcinMuszynski]

I have an excel workbook with many sheets.

I am trying to make a summary on the first sheet that will contain information from the rest of the sheets. It is a bit more complicated by I am illustrating the problem in a different way so you can try it yourself:

If you enter the following:

A1 U

B1 S

C1 A

A4 ="=A1&B1&C1"

Then copy A4 and 'paste special' into C4, C4 will show: =A1&B1&C1

You will have to click into C4 and hit enter so it returns: USA

In the alternative, you can copy C4, paste into notepad, copy and paste back to C4 and it will work as well.

I am writing a macro with many cells and I just cannot click each cell and hit enter.

Please try it yourself before posting that it is a manual / auto calculation issue - it is not.

[#2 · Accepted solution]

@MarcinMuszynski 

You could use this:

Range("C4").Formula = Range("A4").Value

This could be expanded into a loop of course.

[#3]

@Hans Vogelaar thank you. This is a very clean solution.

I have found other workarounds - text to columns and find a replace with any term, e.g. "=" being replaced with the same term. However yours is the best.

Problem solved!