Sep 11 2022 12:19 AM - edited Sep 11 2022 12:22 AM
I am using Office365 and often encounter formulas in some cells of the spreadsheet.
As shown in the picture, cell AA7 shows the formula while the other cells don't, currently the position cursor is in cell AA9 which is the cell containing the formula (seen on the formula bar) but there is no phenomenon like cell AA7 .
I have tried toggling the mode display formula on and off, but to no avail.
Usually I have to copy the cell without errors over the cell, for example, where copying cell AA9 and then pasting it into cell AA7 and then editing the formula, the phenomenon will no longer occur.
Please tell me the cause of this phenomenon and how to fix it.
Thank you.
Sep 11 2022 12:24 AM
Solution@hoacvxd I suspect the cells concerned are, perhaps accidentally, formatted as text. change the format to General, press F2 to enter the formula bar and press Enter to confirm the formula.
Sep 11 2022 12:30 AM
Sep 21 2022 05:57 AM
Sep 21 2022 06:08 AM
@hoacvxd Don't understand. Can you share the file? Upload it in the Forum or share it via OneDrive or similar.
Oct 20 2022 01:32 AM - edited Oct 20 2022 01:38 AM
Oct 20 2022 01:40 AM
@hoacvxd Downloaded your file. It showed this:
Calculation set to Automatic, cell format "General". Needed to go into the formula bar (F2) and press enter. Don't really understand why. Can't replicate it on my own system. Sorry.
Oct 20 2022 02:28 AM
I create a formula using VBA similar to the following code:
Sub testFormula()
Dim strF As String
strF = "=A1,=B1,=C1"
Range("A2:C2").Formula = Split(strF, ",")
End Sub
Although the format is general and press F2 to show the value, every time the macro is run, the above situation appears.
Oct 20 2022 02:40 AM
@hoacvxd Aha, I'll gladly turn you over to a VBA expert as I prefer not to touch that subject. But it seems your is inserting texts that only turn to real formulas once you re-confirm them in the Excel sheet (F2 and enter).
Sep 11 2022 12:24 AM
Solution@hoacvxd I suspect the cells concerned are, perhaps accidentally, formatted as text. change the format to General, press F2 to enter the formula bar and press Enter to confirm the formula.