VBA - Defined Name Range

[stapuff]

I have an automation macro that does a lot of updating, cutting, pasting, etc and I have run into a situation that I don't seem to understand why it is happening.

Defined name ranges loose the range.

On sheet CH E3 I have the following formula

=SUMIF(CH_MyRange4,"<>",CH_MyRange3)+SUMIF(CH_MyRange2,"<>",CH_MyRange1)

The following is the formulas I have for each named range

CH_MYRange1 =OFFSET(Champs!$K$13,0,0,COUNTA(Champs!$K:$K),1)
CH_MYRange2 =OFFSET(Champs!$L$13,0,0,COUNTA(Champs!$L:$L),1)
CH_MYRange3 =OFFSET(Champs!$N$13,0,0,COUNTA(Champs!$N:$N),1)
CH_MYRange4 =OFFSET(Champs!$O$13,0,0,COUNTA(Champs!$O:$O),1)

When I click in the formula bar for each of the above the row column is never correct.

CH_MYRange1 =K13:K289
CH_MYRange2 =L13:L36
CH_MYRange3 =N13:N299
CH_MYRange4 =O13:O23

The last row is 281 and each range should be:

CH_MYRange1 =K13:K281
CH_MYRange2 =L13:L281
CH_MYRange3 =N13:N281
CH_MYRange4 =O13:O281


The end result is the sumif formula above fails to show correct results and just shows #REF error.

My thoughts were to re-define the named ranges within my macro.

Your thoughts.....


stapuff

 

[stapuff]

Reguardless - I appreciate you putting me in the right direction. I never thought to use something like you suggested so I learned a couple things with solving this issue.

Again -

I appreciate all your help.

stapuff