CODEWITHSUNDEEP
vbaexcelexcel-formulanumber-formatting
Tomasz
Tomasz

Reputation: 343

Excel forcing Text NumberFormat on a cell with Formula

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.

Upvotes: 0

Views: 829

Answers (2)

chillin
chillin

Reputation: 4486

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.

Upvotes: 1

MT32
MT32

Reputation: 677

Im not sure if this helps, but I encountered the similar problem in the past, where Excel would automatically switch back the numberformat according to its own judgement.

So what i did was this: say I have two columns

A          B
abc       123
asuh      412
auw       9781
ija       124

I would set the number of another column, column C, as general

Range("C:C").Select
Selection.NumberFormat = "general"

And then copy A or B into it. In your case it would be to automate the code onto it.

And it didnt turn back into either text or number again. For me at least.

Upvotes: 1

Related Questions