Problem with formatting cells

[Arun Balakrishnan]

Hi

I have to export a grid with a varying number of rows and columns. When navigating though the grid I am using the Sheet.Range(irow1,icol2)
type of commands to access various cells.

When I try to set the format of cells in the output Excel document using this method, the formatting doesnt work.

Also if I turn on errors in the Debug option of VB.NET, I keep getting the error "mscorlib.dll Input string is not in a correct format".

The sections of code giving me problem are
sheet.Range(iRow, iCol + 1).value2 = 10000

sheet.Range(iRow, iCol1, iRow, iCol2).NumberFormat = "#,0"

I tried using ".Number" instead of ".value2". The error does not disappear.However if I tuen errors off, then I am unable to catch the error even if I have a Try Catch block i.e. The error seems to be silently swallowed.

Can we access ranges in Excel only through A1, A2 etc instead of using numeric indexes e.g. irow,icol ? (All the examples in your documentation use A1,A2 etc)

If not what am I doing wrong?

Thanks in advance for all your help.

Regards
Arun

[Administrator]

Hi Arun,

I am not sure if you got this problem solved already. If not, here is the solution:

When using the range, use the following syntax:

sheet.Range(sheet.cells(iRow, iCol1), sheet,cells(iRow, iCol2)).NumberFormat = "#,0"

This should work.

Ganesh Kesavan

>Hi
>
>I have to export a grid with a varying number of rows and columns. When navigating though the grid I am using the Sheet.Range(irow1,icol2)
>type of commands to access various cells.
>
>When I try to set the format of cells in the output Excel document using this method, the formatting doesnt work.
>
>Also if I turn on errors in the Debug option of VB.NET, I keep getting the error "mscorlib.dll Input string is not in a correct format".
>
>The sections of code giving me problem are
>sheet.Range(iRow, iCol + 1).value2 = 10000
>
>sheet.Range(iRow, iCol1, iRow, iCol2).NumberFormat = "#,0"
>
>I tried using ".Number" instead of ".value2". The error does not disappear.However if I tuen errors off, then I am unable to catch the error even if I have a Try Catch block i.e. The error seems to be silently swallowed.
>
>Can we access ranges in Excel only through A1, A2 etc instead of using numeric indexes e.g. irow,icol ? (All the examples in your documentation use A1,A2 etc)
>
>If not what am I doing wrong?
>
>Thanks in advance for all your help.
>
>Regards
>Arun