Changing Number Format

  • Thread starter Thread starter Howard Kaikow
  • Start date Start date
H

Howard Kaikow

I am inserting stuff into some cells as follows:

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

Then, I am trying to change some of the cells to numeric format.
What do I need to use instead of the following?

shtExcel.Range(Cells(lngFormat, 2), Cells(lngFormat + lngHigh,
3)).NumberFormat = "#,##0.00"
 
Howard Kaikow said:
I am inserting stuff into some cells as follows:

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

Then, I am trying to change some of the cells to numeric format.
What do I need to use instead of the following?

shtExcel.Range(Cells(lngFormat, 2), Cells(lngFormat + lngHigh,
3)).NumberFormat = "#,##0.00"
Click to expand...


It seems that I need to do the equivalent of "Convert To Number", before
doing the formatting, but I have not yet found that in the Excel object
model.
 
Hey Howard, here's your answer.

Add code like the following before setting the format.

For Each rngCell In shtExcel.Range(Cells(lngFormat, 2),
Cells(lngFormat + lngHigh, 3))
rngCell.Value = rngCell.Value
Next rngCell

This converts the cells from text to numeric format.
 
Hi Howard,

no need to loop

With shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5))
..Value = .Value
End With

In passing, if shtExcel does not refer to the active sheet you may also need
to qualify each "Cells" with shtExcel.

Regards,
Peter T
 
Back
Top