I am inserting stuff into some cells as follows:

shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

Then, I change the cells in columns 2-5 to numeric formattrying to change
some of the cells to numeric format. using code like

With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
.Value = .Value
End With

Then assigning the relevant format for the cells in eaxh column.

However, the cells in column 1 are supposed to retain Text format, but some
cells have all numbers, e.g., 000, and are intended to stay as Text.

If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
goal while the workbook is open.
However, if I save the workbook, exit Excel, then re-open the workbook, the
NumberAsText propety reverts to whatever the user has chosen.

How can I programmatically change numbers to Text format?
If I save the value in a string variable, then assign to the Value property,
the cell is treated as a number, e.g., 000 becomes 0.

Is there a way to do the deed without changing a user's NumberAsText
property?




--
http://www.standards.com/; See Howard Kaikow's web site.

I did not understand your problem correctly but whenever and where eve
you need to conver a value to text format you may use *CStr* function
Example is as follows:
Range("A2")=Cstr(Range("A1"

--
gajendra_vb
-----------------------------------------------------------------------
gajendra_vba's Profile: http://www.excelforum.com/member.php...fo&userid=2993
View this thread: http://www.excelforum.com/showthread.php?threadid=49686

"gajendra_vba"
wrote in message
news:gajendra_vba.20u7n0_1135919702.6814@excelforu m-nospam.com...

I did not understand your problem correctly but whenever and where ever
you need to conver a value to text format you may use *CStr* function.
Example is as follows:
Range("A2")=Cstr(Range("A1")

I tried

For Each rngCell In .Range(Cells(lngFormat, 1),
Cells(lngFormat + lngHigh, 1))
With rngCell
.Value = CStr(.Value)
End With
Next rngCell

Did not change the format.

I cannot use NumberAsText anyway, because that property is not in Excel 97
or Excel 2000.
Property is only in Excel 2002 and 2003.

I would prefer to support all 4 versions, tho I'd be willing to ditch
support for Excel 97, if there was no alternative.

Is there a single command that will convert a multicell range with numerical
entries from values stored as numbers to values stored as text? I don't
believe there is.

You can always loop and prepend a single quote.

--
Regards,
Tom Ogilvy

"Howard Kaikow" wrote in message
...
I cannot use NumberAsText anyway, because that property is not in Excel 97
or Excel 2000.
Property is only in Excel 2002 and 2003.

I would prefer to support all 4 versions, tho I'd be willing to ditch
support for Excel 97, if there was no alternative.

"Tom Ogilvy" wrote in message
...
Is there a single command that will convert a multicell range with
numerical
entries from values stored as numbers to values stored as text? I don't
believe there is.

You can always loop and prepend a single quote.

Thanx, I fergot about that, I'll give it a try.

I tried

.Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
1)).NumberFormat = "@"
For i = 0 To lngHigh
With Cells(lngFormat + i, 1)
If IsNumeric(.Value) Then
strTemp = CStr(.Value)
.Value = "'" & strTemp
End If
End With
Next i

Excel still insists on marking the cells as errors.

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

The NumberAsText property can be overridden by the user's options, not to
mention the property is not supported by Excel 97 and 2000.

Hi Howard,

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

That would be the Errors collection for the range:

rng.Errors(XlErrorChecks.xlNumberAsText).Ignore = True

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

It's been a while, so I guess there's no solution.

Hi Howard,

Don't see the original post :-(

Do you mean something like:

Sub b()
Dim s As String

s = Format$(1.2345, "$0.000")
Debug.Print s
End Sub

or

Sub c()
Dim s As String

s = Format$(1234568, "00-00-00-00")
Debug.Print s
End Sub

Perhaps I'm missing something. As I say I can't see your original post.

Regards,

Peter Beach

"Howard Kaikow" wrote in message
...
It's been a while, so I guess there's no solution.

"Peter Beach" wrote in message
...
Hi Howard,

Don't see the original post :-(

Do you mean something like:

Sub b()
Dim s As String

s = Format$(1.2345, "$0.000")
Debug.Print s
End Sub

or

Sub c()
Dim s As String

s = Format$(1234568, "00-00-00-00")
Debug.Print s
End Sub

Perhaps I'm missing something. As I say I can't see your original post.


The following is the original post.
---------------------------
I am inserting stuff into some cells as follows:

shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

Then, I change the cells in columns 2-5 to numeric formattrying to change
some of the cells to numeric format. using code like

With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
.Value = .Value
End With

Then assigning the relevant format for the cells in eaxh column.

However, the cells in column 1 are supposed to retain Text format, but some
cells have all numbers, e.g., 000, and are intended to stay as Text.

If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
goal while the workbook is open.
However, if I save the workbook, exit Excel, then re-open the workbook, the
NumberAsText propety reverts to whatever the user has chosen.

How can I programmatically change numbers to Text format?
If I save the value in a string variable, then assign to the Value property,
the cell is treated as a number, e.g., 000 becomes 0.

Is there a way to do the deed without changing a user's NumberAsText
property?

------------------------------
And I followed this with the following post:
------------------------------------------
I tried

.Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
1)).NumberFormat = "@"
For i = 0 To lngHigh
With Cells(lngFormat + i, 1)
If IsNumeric(.Value) Then
strTemp = CStr(.Value)
.Value = "'" & strTemp
End If
End With
Next i

Excel still insists on marking the cells as errors.

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

The NumberAsText property can be overridden by the user's options, not to
mention the property is not supported by Excel 97 and 2000.