Range selection problem
 

I am writing a routine to import data from one workbook to another and run
checks on it. This will be done regularly, so the old data has to be first
deleted from the destination workbook. The latest code is below. If I
comment out everything down to "Now import new data", it runs OK. When I
remove the comments, it crashes at the line indicated with "Compile error -
variable not defined". The x1Down in that line is highlighted. I don't
understand this, as the lines seem identical. What am I missing?


' First, delete old data
Application.ScreenUpdating = False
Windows("Broker return example.xls").Activate
Sheets("Checking (1)").Select
Range("A8").Select
Range(Selection, Selection.End(x1Down)).Select <<<<<==== This does not
work
Selection.EntireRow.Delete

' Now check the new data




' Now import new data

Windows("Broker return example.xls").Activate
Sheets("Sheet1").Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select <<<<<<==== This
works
Selection.Resize(, Selection.Columns.Count + 17).Select
Selection.Copy
Windows("Proforma bordereau.xls").Activate
Sheets("Checking (1)").Select
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range selection problem
 

You have x - ONE - down

it should be x - L - down

xlDown versus
x1Down

--
Regards,
Tom Ogilvy

"Brian" wrote in message
...
I am writing a routine to import data from one workbook to another and run
checks on it. This will be done regularly, so the old data has to be first
deleted from the destination workbook. The latest code is below. If I
comment out everything down to "Now import new data", it runs OK. When I
remove the comments, it crashes at the line indicated with "Compile
error -
variable not defined". The x1Down in that line is highlighted. I don't
understand this, as the lines seem identical. What am I missing?


' First, delete old data
Application.ScreenUpdating = False
Windows("Broker return example.xls").Activate
Sheets("Checking (1)").Select
Range("A8").Select
Range(Selection, Selection.End(x1Down)).Select <<<<<==== This does
not
work
Selection.EntireRow.Delete

' Now check the new data




' Now import new data

Windows("Broker return example.xls").Activate
Sheets("Sheet1").Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select <<<<<<==== This
works
Selection.Resize(, Selection.Columns.Count + 17).Select
Selection.Copy
Windows("Proforma bordereau.xls").Activate
Sheets("Checking (1)").Select
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range selection problem
 

If you put Option Explicit at the top of your module, you'd find that

x1Down

is highlighted when you compiled.

xLDown

is a defined constant,

x(one)Down

isn't.



In article ,
"Brian" wrote:

I am writing a routine to import data from one workbook to another and run
checks on it. This will be done regularly, so the old data has to be first
deleted from the destination workbook. The latest code is below. If I
comment out everything down to "Now import new data", it runs OK. When I
remove the comments, it crashes at the line indicated with "Compile error -
variable not defined". The x1Down in that line is highlighted. I don't
understand this, as the lines seem identical. What am I missing?


' First, delete old data
Application.ScreenUpdating = False
Windows("Broker return example.xls").Activate
Sheets("Checking (1)").Select
Range("A8").Select
Range(Selection, Selection.End(x1Down)).Select <<<<<==== This does not
work
Selection.EntireRow.Delete

' Now check the new data




' Now import new data

Windows("Broker return example.xls").Activate
Sheets("Sheet1").Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select <<<<<<==== This
works
Selection.Resize(, Selection.Columns.Count + 17).Select
Selection.Copy
Windows("Proforma bordereau.xls").Activate
Sheets("Checking (1)").Select
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range selection problem
 

Thanks to you both for that simple - and essential - insight. I think my
eyes need to take a break.


"Tom Ogilvy" wrote in message
...
You have x - ONE - down

it should be x - L - down

xlDown versus
x1Down

--
Regards,
Tom Ogilvy

"Brian" wrote in message
...
I am writing a routine to import data from one workbook to another and
run
checks on it. This will be done regularly, so the old data has to be
first
deleted from the destination workbook. The latest code is below. If I
comment out everything down to "Now import new data", it runs OK. When I
remove the comments, it crashes at the line indicated with "Compile
error -
variable not defined". The x1Down in that line is highlighted. I don't
understand this, as the lines seem identical. What am I missing?


' First, delete old data
Application.ScreenUpdating = False
Windows("Broker return example.xls").Activate
Sheets("Checking (1)").Select
Range("A8").Select
Range(Selection, Selection.End(x1Down)).Select <<<<<==== This does
not
work
Selection.EntireRow.Delete

' Now check the new data




' Now import new data

Windows("Broker return example.xls").Activate
Sheets("Sheet1").Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select <<<<<<====
This
works
Selection.Resize(, Selection.Columns.Count + 17).Select
Selection.Copy
Windows("Proforma bordereau.xls").Activate
Sheets("Checking (1)").Select
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False