Jul 24 2021 02:45 PM
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.
Jul 25 2021 03:54 AM
SolutionYou could use this:
Range("C4").Formula = Range("A4").Value
This could be expanded into a loop of course.
Jul 25 2021 07:30 AM
@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!
Jul 25 2021 03:54 AM
SolutionYou could use this:
Range("C4").Formula = Range("A4").Value
This could be expanded into a loop of course.