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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
Click to expand...

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
 
Hi ExcelMonkey,
I simply want to replicate this code so that
the two variables LRow1 and LCol1 have the right information passsed to them
Click to expand...

Should be as simple as:

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

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

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