Error when selecting dynamic range

Hi there.

I hope i`m posting it in the right place but since big "i" balloon forwarded me to this place than it might be
By first i am quite new to VBA but i have one complicated job to do with several excel files.

What i need is to select and count cells from ~19 different excel files and combine it into one workbook. I somehow have done everything to the point where my vba selects data from one file and writes info from it into active workbook
My idea after this step was to write path`s of all files and range("x") from where my scrip should start counting those cell, into 2nd sheet of my active WB and then create Dynamic "wbRem.Sheets(1).range("x").Select.

So my main problem now is that when i run script with "wbRem.Sheets(1).range("C251").Select" it runs with no problems (wbRem = path to other file) but if i run "wbRem.Sheets(1).ransel.Select" it gives me Error "Object doesn`t support this property or method"

ransel will be dynamic but for now i have just like this:
(dim ransel as range
set ransel = range("c251"))

What am i doing wrong and how can i make it right?
And sorry if this is not the right place to look for help.

Best Regards

Post code and/or the workbook with data removed! We can't really tell without at least something to look at!

I guess nobody can help me

might be anyone can suggest another method how to link several workbooks that have similar structure but different ranges?

Sorry I haven't replied!

Try putting this before the statement providing the error (without the ...'s):

Please Login or Register  to view this content.

You have to make the sheet active before you can use the Select. I suspect that's causing your problems...

Well it does not help for me.
It gives the same error "Object doesn`t support this property or method"

Please Login or Register  to view this content.

I created test macro. It gives the same error. Even if you comment that 'active part it still gives the same error. but it should not.

Ps. And i guess adding wbRem.active is a bad idea. If i make that opened Workbook active than all my data collected would be written on that workbook instead of the workbook with macro itself would`t it?

Ahhh, your sample should go like this:

Please Login or Register  to view this content.

You should find that runs without error. I think maybe you are not understanding the hierarchical object model of Excel. The object model starts of with an Application object, which can have one or many workbooks. Each Workbook can have one or many sheets. A range belongs to a particular sheet, it is not independent of a sheet. So you can't have a range defined in code (your "ran" variable) that you can use across workbooks. Each time you change the workbook, you must change the definition of the "ran" with a Set statement. Maybe you understand this, maybe you don't, I aren't being patronising, it just looks like maybe you don't understand from your code.

In this way, when you define a Workbook, it will pay to define the worksheet as well as your range. And to do so, use the higher level object. An example is below:

Please Login or Register  to view this content.

Now you should be able to see the cascading hierarchy clearly. The Application you don't need to define as it defaults to your current application. The Workbook is next level down, define it first. Use the Workbook to define the Worksheet. Then use the Worksheet to define the range. If you always do it this way it becomes a lot less confusing!

It seems you were right about me understanding hierarchy clearly. Well i guess now i am starting to understand a bit more.
one more question. why - '（note here it is often better to use a name of a worksheet instead, i.e. wb.Worksheets("WorksheetName"))
If i use names than i have to make the same name for sheets in all my 20 workbooks. i guess in this case names are not the best way.

And thx a lot blobbles

Yes, it depends on your situation. Just note though that when you say "Worksheets(1)", this *may* mean different worksheets for different workbooks. Worksheet(1) is always the first worksheet listed in the book, but if a workbook has had a few sheets deleted and new sheets created, it can be hard to know what Worksheet(1) refers to!

It really pays when you are coding to remember the hierarchy, have it in your head at all times. Application->Workbook->Worksheet->Cell/Range

In the end i did not check your suggestion about hierarchy, but instead i forced everyone using those files to make changes so they look the same.
If anyone needs this for any reason here is code to get file links from Sheet(2) and using those links opens files and counts things that i have mentioned under IF`s

Thx a lot blobbles.

Please Login or Register  to view this content.