Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Run time error 9 : Subscript out of range | Excel Discussion (Misc queries) | |||
My Quest with Time!! | Excel Worksheet Functions | |||
set or reset the print range for several sheets at a time? | Excel Discussion (Misc queries) |