Changing Number 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 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]

"Howard Kaikow" <[email protected]> wrote in message
news:[email protected]...
> 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"


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.

[Howard Kaikow]

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.

[Peter T]

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

"Howard Kaikow" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>