range object as a function parameter problem

**guss** · 03-30-2011, 04:36 PM

Not sure why this is not working, as one can see I am simply trying to pass a range object but I get this error. What am I missing here?

runtime error 1004
method 'range' of object '_Worksheet' failed

Please Login or Register  to view this content.

**tigeravatar** · 03-30-2011, 04:45 PM

it looks like you're trying to get the highest number in cell myrange of all the worksheets. I'd recommend using this instead:

Please Login or Register  to view this content.

Basically the change is to pass a string that is the desired cell coordinate instead of trying to pass a range.

Hope this helps,
~tigeravatar

**guss** · 03-30-2011, 04:54 PM

Greetings tigeravatar,
Sorry I should have made it clear this is just a useless piece of code I am using to practice and am not actually using it for anything. Can you explain why passing a range object and then referencing as I am trying to do but failing miserably won't work. I have tried passing a string with success but as I have said I am trying to understand the language.

Cheers

**romperstomper** · 03-30-2011, 05:04 PM

By passing a Range object, you are passing a specific cell (or cells) on a specific sheet. If you want to refer to cells in the same location on another sheet, you need to refer to MyRange's address:

Please Login or Register  to view this content.

**tigeravatar** · 03-30-2011, 05:04 PM

guss,

Aaah, ok. I see what you mean now. The problem is that when you define a range, you generally have to assign it to a worksheet.

Originally stated: "The common workaround for this problem is to assign the range to ActiveSheet, and when you pass it through a function, the function cycles through worksheets and activates them. It would look like this using your example code:"

Edit: That does not work. Thank you to romperstomper for correcting me. I apologize for the confusion.
(incorrect code removed)

**romperstomper** · 03-30-2011, 05:24 PM

@tigeravatar:
Your code does not do what I think you think it does. MyRange always refers to A1 on the sheet that was active at the start of the code, not A1 on whatever sheet is activated during your loop.

**tigeravatar** · 03-30-2011, 05:27 PM

romperstomper,

Oh, you're right. I just tested it (should have done that first, heh) and it does indeed specify itself to the sheet that was active when it was defined. Thanks for the correction, sorry for the confustion

~tigeravatar

**guss** · 03-30-2011, 05:49 PM

Originally Posted by romperstomper

@tigeravatar:
Your code does not do what I think you think it does. MyRange always refers to A1 on the sheet that was active at the start of the code, not A1 on whatever sheet is activated during your loop.

Amazing didn't know this and can see what you are saying, will have to play around with this to fully understand it.

Many thanks romperstomper

and thank you tigeravatar for trying to help me.

Guss