Making a number have Text format

[Howard Kaikow]

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?

 

[gajendra_vba]

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"

 

[Howard Kaikow]

"gajendra_vba" <[email protected]>
wrote in message

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.

 

[Howard Kaikow]

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]

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.

 

[Howard Kaikow]

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.

 

[Howard Kaikow]

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.

 

[Howard Kaikow]

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

 

[Peter Beach]

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]

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.

 

[Stephen Bullen]

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