Using Range Name reference as part of R1C1 Formula

Greetings:

Am currently using the code, shown below.
This worked great until I started using my macro multiple times on one sheet
and multiple times within one workbook.
Obviously, only One cell can be called "NoOfUnits".
All further references to that range name ends up useing the original result.

Please Login or Register  to view this content.

1: How can I best get around this problem?
2: I can place the formula result that is in the cell called NoOfUnits in a fixed location - Say Z5.
Is there a way to mix and match R1C1 type references with standard A1 type references?
For example ( however, this does not work)

Please Login or Register  to view this content.

Thank-you

Smuzoen
Thanks for the response.
I'm getting an error with this method: Run-time error 1004; Application-defined or object-defined error.
I have no idea where (or how) to start looking for this.
Pls. advise..........

Thanks for the response.
My original code, as shown, worked just fine, for a long time already.
However, I am now expanding the use of the code.
It will now be used multiple times within the same workbook.
Once the named range is set, it holds it for all future references within that book.
Consequently, my calculations are out to lunch.

If there is another way of tackling this, that would be OK, as well.
Keep in mind that the numbers of rows involved each time, will vary.

1: You are absolutely correct, of coarse. Statements shouldn't be 'shortened', as mine was.
No doubt some future version will take exception to what today does work.
I'll pass that on to the guru who initially supplied me with this code.
2: As above
3: Named Range. EACH time the macro is run, it is applied to a different data base, with a different number of rows, within the same workbook. Consequently, the formulas do NOT work correctly, since it appears to always reference the original (first) named range.
For example, if the first time I run the macro it indicates that there are 10 rows in use, that is what the named range will refer to.
The next the macro is run, the data could consist of 250 rows, yet this formula will be dividing 10, not by 250, as required.
I hope that makes it a bit clearer.
4: Sorry! I did try R5C26 with and without quotes.
With quotes I get run time error 1004; Method 'Range' of object'_Global' failed.
Without quotes I get Compile Error: Variable not Defined
5: I hope the above clears this one up.

The following shows the macro in question, without all the duplicates used to operate on the multiple columns.
Perhaps that might also help clear up what I'm obviously not expressing very well or perhaps you might see what in my code is perhaps tripping me up.
Thanks for your consideration.

Please Login or Register  to view this content.

Greetings All:

Since we collectively are unable to come to a working solution at this time,
I've tackled the situation the long way round.
The code I've used is shown below, together with some comments.
I hope that this may still help someone else out.

So, I guess you could say that, while this thread had not been solved, the
situation has been 'resolved'.

Thanks for all the efforts.

Please Login or Register  to view this content.

Yes I did.
NoOfUnits, as indicated in initial post is a range name.
I could not get your best guess to work - Sorry.

That was exactly the case.
However, your best guess DOES NOT WORK!!!
It was one of the first efforts I made myself before ever posting, along with dozens of other tries.
Maybe the problem has to do with the fact that I'm still running Excel 2003. ???
I appreciate your efforts, but guessing has to stop at some point.

Over and out.