Scientific Notation in Text Cell after Fill

[Dennis]

Using Office 97

After using in a Sub Routine:

myCell.FormulaR1C1 = myCell.Offset(-1, 0).FormulaR1C1

Cells with 10+ numeric characters are displayed
with Scientific Notation even though the Cell Format
is "Text" which is the format that I want in this case.

In Excel, if I select the cell AND click in the Formula Box
then the display of the cell changes instantly to a normal
text display, i.e., left aligned with no scientific
notation.

I did attempt to record a macro and used the code by
placing it in my Sub Routine but still Scientific Notation.

In VBA, what can I do to properly display a "text" cell?

TIA Dennis

 

[PegL]

Add a line above the one you have that says
myCell.NumberFormat="@"
I have XP, but I believe that will also work in 97.

 

[Guest]

I appreciate your answer.

It did properly display without the Scientific Notation
But it also changed the display of dates and other formats.

In addition, I noted that the specific column is formated
as a number unless there is an alpha character inbeded in
the number sequence.

My routine inly acts on empty cells.

That said, it is the non-empty cells that also change to
scientific notation.

What VBA code can I use to re-do a mixed format column so
that all cells are text AND that the display of those
cells are Text?

It seems that I need to copy>paste special but that does
not change the scientific-notation display?

It seems to me that XL 97 does have this issue but I do
not know the easy way to work around this weakness?

TIA Dennis

 

[Dave Peterson]

Maybe you can steal the .formulaR1C1 and the .NumberFormat???

myCell.FormulaR1C1 = myCell.Offset(-1, 0).FormulaR1C1

becomes:

with mycell
.numberformat = .offset(-1,0).numberformat
.formulaR1C1 = .offset(-1,0).formular1c1
end with

 

[Dennis]

Thanks Dave
Dennis

-----Original Message-----
Maybe you can steal the .formulaR1C1 and the .NumberFormat???

myCell.FormulaR1C1 = myCell.Offset(-1, 0).FormulaR1C1

becomes:

with mycell
.numberformat = .offset(-1,0).numberformat
.formulaR1C1 = .offset(-1,0).formular1c1
end with





--

Dave Peterson
(e-mail address removed)
.