Run Time Error 9 (Subscript out of Range) for XLA file
 

I just turned an xls file into an xla file and am having some issues with my
code. The code snippet below is failing on: Windows(varfile1).Activate.
varfile1 is the name of the xla file. When it was an xls file I selected the
workbook, then selected a sheet in it, then selected a start cell and then
figured out what the used range was (I know I can also employ .UsedRange but
will ignore for now). However, now that its an xla file I cannot seem to a
get to my start cell (within a sheet in the xla file) by selecting the xla
file and its sheet.

How do I get around this. Thanks in advance.

Windows(varfile1).Activate
Sheets(varsheet1).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Hi ExcelMonkey,

I just turned an xls file into an xla file and am having some issues with my
code. The code snippet below is failing on: Windows(varfile1).Activate.
varfile1 is the name of the xla file.

You cannot select an invisible window (all windows of an add-in are hidden),
you must work with the Workbook object instead. There is no need to select
things though. This snippet copies the "used range" of sheet varsheet1:

With Workbooks(varfile1).Sheets(varsheet1)
.Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell)).Copy
end With

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

So for the sake of completeness I simply want to replicate this code so that
the two variables LRow1 and LCol1 have the right information passsed to them
now that I am in an xla not an xls. The issue here is that I am working with
someone elses code and have to integrate it with mine and release it very
quickly. Will come back later and optimise. But for time being, I simply
want to ensure I can replicate this within an xla file. Can you provide me
with some guidance on how to do that? Thanks!


Windows(varfile1).Activate
Sheets(varsheet1).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
With Selection
.MergeCells = False
End With
Range("A1").Select
LRow1 = ActiveCell.SpecialCells(xlLastCell).Row
LCol1 = ActiveCell.SpecialCells(xlLastCell).Column

"Jan Karel Pieterse" wrote:

Hi ExcelMonkey,

I just turned an xls file into an xla file and am having some issues with my
code. The code snippet below is failing on: Windows(varfile1).Activate.
varfile1 is the name of the xla file.

You cannot select an invisible window (all windows of an add-in are hidden),
you must work with the Workbook object instead. There is no need to select
things though. This snippet copies the "used range" of sheet varsheet1:

With Workbooks(varfile1).Sheets(varsheet1)
.Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell)).Copy
end With

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi ExcelMonkey,

I simply want to replicate this code so that
the two variables LRow1 and LCol1 have the right information passsed to them


Should be as simple as:

LRow1 =
Workbooks(varfile1).WorkSheets(varsheet1).Range("A 1").SpecialCells(xlLastCell)
.Row

LCol1 =
Workbooks(varfile1).WorkSheets(varsheet1).Range("A 1").SpecialCells(xlLastCell)
.Column
Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com