SOLVED

Formula is not active until double clicked

%3CLINGO-SUB%20id%3D%22lingo-sub-2583445%22%20slang%3D%22en-US%22%3EFormula%20is%20not%20active%20until%20double%20clicked%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2583445%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20workbook%20with%20many%20sheets.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20a%20summary%20on%20the%20first%20sheet%20that%20will%20contain%20information%20from%20the%20rest%20of%20the%20sheets.%20It%20is%20a%20bit%20more%20complicated%20by%20I%20am%20illustrating%20the%20problem%20in%20a%20different%20way%20so%20you%20can%20try%20it%20yourself%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20enter%20the%20following%3A%3C%2FP%3E%3CP%3EA1%20U%3C%2FP%3E%3CP%3EB1%20S%3C%2FP%3E%3CP%3EC1%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA4%26nbsp%3B%3D%22%3DA1%26amp%3BB1%26amp%3BC1%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20copy%20A4%20and%20'paste%20special'%20into%20C4%2C%20C4%20will%20show%3A%26nbsp%3B%3DA1%26amp%3BB1%26amp%3BC1%3C%2FP%3E%3CP%3EYou%20will%20have%20to%20click%20into%20C4%20and%20hit%20enter%20so%20it%20returns%3A%20USA%3C%2FP%3E%3CP%3EIn%20the%20alternative%2C%20you%20can%20copy%20C4%2C%20paste%20into%20notepad%2C%20copy%20and%20paste%20back%20to%20C4%20and%20it%20will%20work%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20writing%20a%20macro%20with%20many%20cells%20and%20I%20just%20cannot%20click%20each%20cell%20and%20hit%20enter.%3C%2FP%3E%3CP%3EPlease%20try%20it%20yourself%20before%20posting%20that%20it%20is%20a%20manual%20%2F%20auto%20calculation%20issue%20-%20it%20is%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2583445%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2584378%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20is%20not%20active%20until%20double%20clicked%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1111795%22%20target%3D%22_blank%22%3E%40MarcinMuszynski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ERange(%22C4%22).Formula%20%3D%20Range(%22A4%22).Value%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20could%20be%20expanded%20into%20a%20loop%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2584662%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20is%20not%20active%20until%20double%20clicked%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthank%20you.%20This%20is%20a%20very%20clean%20solution.%3C%2FP%3E%3CP%3EI%20have%20found%20other%20workarounds%20-%20text%20to%20columns%20and%20find%20a%20replace%20with%20any%20term%2C%20e.g.%20%22%3D%22%20being%20replaced%20with%20the%20same%20term.%20However%20yours%20is%20the%20best.%3C%2FP%3E%3CP%3EProblem%20solved!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@MarcinMuszynski 

You could use this:

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

This could be expanded into a loop of course.

@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!