> I am having a problem formatting a worksheet via vb.
> I have
>
> Dim XL As Excel.Application
> Dim XLWBk As Excel.Workbook
> Dim XLWS As Excel.Worksheet
>
>     XLWS.Range("A1").Resize(nrec + 1, nsel).Value = newarray
>     XLWS.Range("A1").Select
>     lr = XLWS.Range(Selection, Selection.End(xlDown)).Cells.count
>
>     For i = 1 To nsel
>         If IsNumeric(XLWS.Cells(2, i).Value) Then XLWS.Cells(lr,
> i).NumberFormat = "0.000"
>
>     Next i
>     XLWS.Columns(1, nsel).EntireColumn.AutoFit
>
> Firstly, the lr= gives me an object variable not set  error
>
> Then if I comment that out and change XLWS.Cells(lr to say Cells(1000,
> although the code executes, the resultant spreadsheet does not have its
> numbers formatted to 3 decimal places - sometimes they are 0, sometimes
> they are 2 and 3 for different columns.
>
> Finally when I try to exceute the Autofit for the columns, I get an
> appicaltion or object defined error.
>
> Could someone set me right please?
> Thanks
> Chris

> Hello, inquirer,
>
> I'm assuming that you have left a few lines out for brevity.  (I.e. the
> XL... variables are not set anywhere.)  But with a few such additions
> all the lines shown except the last will work for me.
>
> For the last line, I think that you cannot use Columns the way you are
> trying to.  I would first declare a range variable and apply the Autofit
> to that.  For example, I have copied your code (with a few lines added)
> and the Autofit applied to a Range below.
>
> Cheers,
> Randy
>
>     Dim XL As Excel.Application
>     Dim XLWBk As Excel.Workbook
>     Dim XLWS As Excel.Worksheet
>
>     Set XL = New Excel.Application
>     XL.Visible = True
>     Set XLWBk = XL.Workbooks.Add
>     Set XLWS = XLWBk.Worksheets.Add
>     Dim newarray(3, 4) As Variant
>     Dim intRow As Integer
>     Dim intCol As Integer
>     For intRow = 0 To 3
>         For intCol = 0 To 4
>             newarray(intRow, intCol) = intRow + intCol / 10
>         Next intCol
>     Next intRow
>
>     Dim nrec As Integer
>     Dim nsel As Integer
>     nrec = 3
>     nsel = 5
>
>     XLWS.Range("A1").Resize(nrec + 1, nsel).Value = newarray
>     XLWS.Range("A1").Select
>     lr = XLWS.Range(Selection, Selection.End(xlDown)).Cells.Count
>
>     For i = 1 To nsel
>         If IsNumeric(XLWS.Cells(2, i).Value) Then
>             XLWS.Cells(lr, i).NumberFormat = "0.000"
>         End If
>     Next i
>     '''XLWS.Columns(1, nsel).EntireColumn.AutoFit
>     Dim rngCols As Range
>     Set rngCols = XLWS.Range("A1").Resize(nrec + 1, nsel)
>     rngCols.Columns.EntireColumn.AutoFit
>
>     XLWBk.Close SaveChanges:=False
>     XL.Quit
>
>
> inquirer wrote:
>
>> I am having a problem formatting a worksheet via vb.
>> I have
>>
>> Dim XL As Excel.Application
>> Dim XLWBk As Excel.Workbook
>> Dim XLWS As Excel.Worksheet
>>
>>     XLWS.Range("A1").Resize(nrec + 1, nsel).Value = newarray
>>     XLWS.Range("A1").Select
>>     lr = XLWS.Range(Selection, Selection.End(xlDown)).Cells.count
>>
>>     For i = 1 To nsel
>>         If IsNumeric(XLWS.Cells(2, i).Value) Then XLWS.Cells(lr,
>> i).NumberFormat = "0.000"
>>
>>     Next i
>>     XLWS.Columns(1, nsel).EntireColumn.AutoFit
>>
>> Firstly, the lr= gives me an object variable not set  error
>>
>> Then if I comment that out and change XLWS.Cells(lr to say Cells(1000,
>> although the code executes, the resultant spreadsheet does not have
>> its numbers formatted to 3 decimal places - sometimes they are 0,
>> sometimes they are 2 and 3 for different columns.
>>
>> Finally when I try to exceute the Autofit for the columns, I get an
>> appicaltion or object defined error.
>>
>> Could someone set me right please?
>> Thanks
>> Chris

> Randy thanks for your help.
> I have modified my code so that it is the same as yours but everytime I
> execute it I get runtime error 91
> Object variable or With block variable not set.
>
> I have stepped thru the code and everything is ok till it fails at
> lr = XLWS.Range(Selection, Selection.End(xlDown)).Cells.Count
>
> Chris

> Hello, Chris,
>
> It took a little experimenting, but I think that I see the problem.  I
> found that I can sometimes get this error when there is another Excel
> session operating.  If I repeat the run of the routine without first
> terminating the VB application I can generate it consistently.  (It
> looks as if an instance of Excel remains even though XL.Quit has been
> specified.)
>
> I suspected the problem was with the unqualified "Selection" property.
> This is supposed to default to the "Application" object, but it doesn't
> specify which Application object.  My guess is that it finds the "other"
> instance of Excel (and also holds a reference to this until the VB
> application terminates).
>
> The solution is simply to qualify the "Selection" property -- not a bad
> practice in any case.  So try:
>
>     lr = XLWS.Range(XL.Selection, XL.Selection.End(xlDown)).Cells.Count
>
> This seemed to cure the problem for me (and the stray instance of Excel
> no longer shows up in the Task Manager "Processes" window  :-) ).
>
> Cheers,
> Randy
>
>
> inquirer wrote:
>
>> Randy thanks for your help.
>> I have modified my code so that it is the same as yours but everytime
>> I execute it I get runtime error 91
>> Object variable or With block variable not set.
>>
>> I have stepped thru the code and everything is ok till it fails at
>> lr = XLWS.Range(Selection, Selection.End(xlDown)).Cells.Count
>>
>> Chris