comparing two list ranges in two workbooks
Posted by Ben on April 11, 2000 11:06 AM
I've come across something i don't understand.
I'm comparing 2 lists (single columns) that are in 2 different workbooks; when i try to set the ranges in code, i keep getting error 1004. now if I manually or code-wise place the two columns next each other on the same workbook, i have no problems.
e.g. this works OK after paste operation:
Set rngFirst = ActiveWorkbook.Worksheets(1).Range(Range("A1"), Range("A1").End(xlDown))
Set rngSecond = ActiveWorkbook.Worksheets(1).Range(Range("B1"), Range("B1").End(xlDown))
but this doesn't:
Set rngFirst = Workbooks("Blah").Worksheets(1).Range(Range("A1"), Range("A1").End(xlDown))
Set rngSecond = Workbooks("Rhubarb").Worksheets(1).Range(Range("A1"), Range("A1").End(xlDown))
can anyone tell me why?
Posted by Ivan Moala on April 12, 2000 12:58 AM
Ben
This use to stump me. You must fully qualify the arguments. For example, in your code it would fail if workSheet(1) is not the active sheet:
This is because the Range property defaults to the active sheet if no sheet is provided to qualify it. If Sheet1 is not active at run time, the outer Range property and the inner Range property will be referring to different sheets.
ie. Set rngFirst = Workbooks("Blah").Worksheets(1).Range(Range("A1"), Range("A1").End(xlDown))
the Range("A1") and Range("A1").End(xlDown)) would be referening to diff. sheets.
The correct way to handle this is shown in the following example:
Sub test3()
With Workbooks("Blah").Worksheets(1) '.Sheets
Set rngfirst = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With
With Workbooks("Rhubarb").Worksheets(1) '.Sheets
Set rngsecond = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With
End Sub
Ivan
