PDA

View Full Version : Solved: Setting range syntax



Zack Barresse
09-14-2004, 03:58 PM
Okay, end of a very long day. Having syntax issues - yet again. The following line is giving me problems ...
Set rng = wb.Sheet2.Range("A2:A" & wb.Sheet2.Range("A65536").End(xlUp).row)
wb is a workbook, set as Set wb = Workbooks("WkbkName.xls"). I get a error 438, object doesn't support this property or method. Does anybody know what I'm doing here, I'm sure it's simple..

Daniel Klann
09-14-2004, 04:01 PM
G'day mate, just a little change needed:-


Set rng = wb.Sheets("Sheet2").Range("A2:A" & _
wb.Sheets("Sheet2").Range("A65536").End(xlUp).Row)


Dan

Zack Barresse
09-14-2004, 04:04 PM
Hi Dan, thanks for that! So, to pick your brain here .. you're saying just change the way I'm referencing the sheet?

Jacob Hilderbrand
09-14-2004, 05:27 PM
The code name of the sheet is not a collection item of the workbook. The sheet tab name is, but the code name is a class module in the VBE. If you type wb. you will see that there are no sheet code names listed. Seems odd to me, but that's the way it is.

You can set a worksheet variable and include the workbook:


Set ws = Workbooks("WkbkName.xls"). Sheets("Sheet2")
Set rng = ws.Range("A2:A" & ws.Range("A65536").End(xlUp).Row)

Zack Barresse
09-14-2004, 08:21 PM
Thanks Jake, that's what I ended up going with. Interesting how that syntax plays out. I figured they (MS) would have allowed for both ways. Oh well. Thanks. :)