Problemas with object assignation in hierachy Range.Offset.Range

I have been experiencing some problems with this hieranchy: Range.Offset.Range, and I would like to know if anyone knows why this could be happening. I have several ways to make my function work, but I would like to know if there is an specific reason for such error.

Here's the thing: I am using a range called "MPOS_XX", where XX is an integer between 01 and 30, as relative reference. Then I need to work with some data near this range. In order to ease the syntaxis, I decided to assign this offset to a Range Object. The last three rows of the code reflect what I do. This function is not used in the worksheet, it is used in a module.

Please Login or Register  to view this content.

The problem is that whenever a run my code, error 1004 appears! Curious thing: If I go to the workbook and select the worksheet WS_02, the code runs smoothly without problems.

Before you post any comments, be aware of the following:

Please Login or Register  to view this content.

This is not what I intend to do! This syntasis references a different group of cells from what I need.

Hope anyone has an idea.

In case you need to run the code, you will need this function:

Please Login or Register  to view this content.

In case you need the workbook, it is attached to the message.

Regards,

Yeap.. the problem is gone. Better syntaxis and better use of the Range object properties. I Like it!

Though, any ideas on the original problem?

In case someone else is working on this, there was a little problem in the function. Trying to find a different solution, I worked this function as a subroutine. Please consider the following modification to the loop:

Please Login or Register  to view this content.

Izandol, thanks foryour suggestion.

Regarding the first block of code, please consider my original post. That syntaxis refereces other cells, different from what I need.

My question is focussed on the behavior of VBA using the hierachy Range.Offset.Range(cells, cells); it should work either using addresses or the property 'Cells'. Considering the object logic, Range.Offset yields a Range object. And the Range object has an Range property which I can access using addresses or the property cells.

Izandol,

You were absolutely right. I have not seen that the solution you suggested is different from what I wrote. And it worked perfectly. After internalizing the error, I think that VBA was in conflict 'cause both the worksheet and the range object have a Cells property. Of course, when I selected the worksheet in the workbook, I thing VBA choose the Worksheet over the range object to reference the Cells property.

Thnaks for your support, both Izandol and Kaper! Administrator, you can consider this thread as solved!

Regards,

Rubén