I am trying to store a formula in a cell. The cell has a NumberFormat set to "@" i.e. Text. When I do:
TargetSheet.Cells(Row, Col).Formula = "=A7"
And A7 has in it "1805R03-01" I get "=A7" displayed in the in the cell.
Even though I think you should be allowed to define formulas for a Text formatted field if you set it through the Formula property, I changed the code to:
(1) TargetSheet.Cells(Row, Col).NumberFormat = "General"
(2) TargetSheet.Cells(Row, Col).Formula = "=A7"
And that palatially got me what I wanted, as now my cell displays the correct value of "1805R03-01" but the number format is set by Excel again to "@" so that if I hit enter on that cell the display returns to "=A7". I could re-add Line (1) as and additional Line (3) in my code above, but that just means that I will stop the conversion of my formula back to text one time, hitting enter twice on that cell still results in "=A7". How do I stop Excel from forcing the Text number format on a cell? The cell displays correctly when I manually return the format for it to "General" so a Text number format is not needed.
If cell A7's number format is text, try setting it to general. Then re-enter the formula in cells(row, col) and check if you get different results.
Dependent cells (in the context of formula auditing) seem to inherit the NumberFormat of their precedents -- unless the precedent cell's NumberFormat is general.