changing cell references

**Hein** · 01-30-2009, 05:47 AM

Hi all

In my procedure I have a loop that, at a certain point, makes a named range.
The code I use is:

Please Login or Register  to view this content.

Later on in the procedure I use this (in this case) profitcenterCell in a formula that is defined in the code.

My goal is that in loop 1 the cell reference in the formula is (e.g.) $B$2, in the second loop $L$2, 3rd: $V$2 etc

My formula:

Please Login or Register  to view this content.

The 1st time the code runs (reference $B$2) it runs OK
The other times it doesn't
The problem is that the cell ProfitCenterCell changes location, also disrupting the 1st loop

How can I change the last formula so that it doesn't refer to ProfitCenterCell but to $B$2 in the first loop, in the second loop $L$2, 3rd: $V$2 etc.?

Thanks in advance
Hein

**royUK** · 01-30-2009, 05:51 AM

You need to post the relevant code

**Hein** · 01-30-2009, 06:08 AM

Hi Roy

I will make a demo, but that takes some time, and I have the afternoon free

I will make my demo and post it coming monday
Hope to see then

Have a good weekend
Hein

**Hein** · 02-02-2009, 05:18 AM

Hi Roy (and others of course :-)

I've made the demo as you requested
I have added comments in the code
Hopefully they make sense, if you have questions please ask them and I will try to clarify it.

Thanks for your help
Hein

**Hein** · 02-03-2009, 03:59 AM

I found a mistake in the Match formula
In the 3rd section of the formula I messed up
I have changed this to "0"

Remains the mistery of the 1st part of the match -formula

Hopefully somebody knows what to do

Hein