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

 

[ChrisM]

When you are using ranges in a SUMIF, all the ranges need to be the same size. Doing COUNTA on two different columns could 'potentially' lead to different range sizes. I would choose just one column to be your main index column, and use that in the COUNTA for each named range. Choose the column you know will never have any blanks in it.

 

[stapuff]

hmmmmm....not sure why it is seeing blanks in the cell where it does.

I will try column D, where there are no potentials for blanks and see how that works out.

I appreciate the response back.

 

[shg]

Consider instead defining:

tbl refers to: =INDEX(Champs!$K:$K, ROW(Champs!$K$12)+1):INDEX(Champs!$O:$O, MATCH(REPT("z", 255), Champs!$K:$K))

CH_MYRange1 refers to: =INDEX(tbl, 0, 1)
CH_MYRange1 refers to: =INDEX(tbl, 0, 2)
CH_MYRange1 refers to: =INDEX(tbl, 0, 4)
CH_MYRange1 refers to: =INDEX(tbl, 0, 5)

That definition is non-volatile, and all ranges are the same size (dictated by col K)

 

[stapuff]

Not what I expected:

When I re-ran the macro I still get the same error in the sumif cell, however,I am getting REF errors in the named range formula's itself.

CH_MYRange1 =OFFSET(Champs!REF,0,0,COUNTA(Champs!$D:$D),1)
CH_MYRange2 =OFFSET(Champs!REF,0,0,COUNTA(Champs!$D:$D),1)
CH_MYRange3 =OFFSET(Champs!REF,0,0,COUNTA(Champs!$D:$D),1)
CH_MYRange4 =OFFSET(Champs!REF,0,0,COUNTA(Champs!$D:$D),1)

stapuff

 

[stapuff]

shg -

I also tried your solution and the sumif does not work correctly. When I cursor into the define named range box where I added your formula "tbl refers to: =INDEX(Champs!$K:$K, ROW(Champs!$K$12)+1):INDEX(Champs!$O:$O, MATCH(REPT("z", 255), Champs!$K:$K))
"
it only shows K12:O13 as being selected and the sumif formula only sums row 13's values.

Your thoughts....

stapuff

 

[shg]

With tbl defined like that, and data like this:

       ---K--- ---L--- ---M--- ---N--- ---O---
  12   Header1 Header2 Header3 Header4 Header5
  13   Data1                                  
  14   Data2                                  
  15   Data3                                  
  16   Data4                                  
  17   Data5                                  
  18   Data6

tbl refers to $K$13:$O$18

CH_MYRange1/2/3/4 refers to columns 1/2/4/5 of that range

 

[stapuff]

I change sheet name to CH from Champs

I define CH_tbl with:
=INDEX(CH!$K:$K, ROW(CH!$K$12)+1):INDEX(CH!$O:$O, MATCH(REPT("z", 255), CH!$K:$K))


I define
CH_MyRange1x =INDEX(CH_tbl, 0, 1)
CH_MyRange2x =INDEX(CH_tbl, 0, 2)
CH_MyRange3x =INDEX(CH_tbl, 0, 4)
CH_MyRange4x =INDEX(CH_tbl, 0, 5)

in sheet CH range E3 is:
=SUMIF(CH_MyRange4x,"<>",CH_MyRange3x)+SUMIF(CH_MyRange2x,"<>",CH_MyRange1x)

Result returns 30,000

Should return 280,000

When I go into the defined names and cursor into CH_tbl only K12:O13 have the line around it....so it is not going to the bottom of the range, which is at row 281.

stapuff

 

[stapuff]

ok....

I change
=INDEX(CH!$K:$K, ROW(CH!$K$12)+1):INDEX(CH!$O:$O, MATCH(REPT("z", 255), CH!$K:$K))


to =INDEX(CH!$K:$K, ROW(CH!$K$12)+1):INDEX(CH!$O:$O, MATCH(9.99999999999999E+307, CH!$K:$K))

the latter only because:

Data range is formatted as numeric
If your range contains only numbers, you could use the following formula:

=INDEX(range,MATCH(9.99999999999999E+307,range))

Thanks,

stapuff

 

[shg]

Ah.

Your use of COUNTA instead of COUNT in the original definition led me to believe the range contained text.

I guess a closer look at the formula would have made clear that Range1 (at least) is numbers.