"Count Megearea columns" code is not working

[shiva_raj]

HI



I wrote a VBA code by using Sumifs function. in this code i find out starting column number (for each category box), ending column number for merged cell by using #selection.columns.count# in 2nd row & 11th row (first attachment). sometimes this formula returns 1 even though there are 6 cells in Mergearea. When we run the code by using F5, this formula is not working properly. in the summary sheet (first attachment) few violet BOXES were not getting result. please help me.

thank you in advance.

Regards
shiva

 

[p45cal]

you seem to have about 5 places with:
n = Cells(2, sc).MergeArea.Columns.Count
where Cells is unqualified.
Try changing all instances to:
n = .Parent.Cells(2, sc).MergeArea.Columns.Count

As an aside, perhaps
lr = Worksheets("Raw").Cells(Rows.Count, 1).End(xlUp).Row
might be better being:
lr = Worksheets("BOXES").Cells(Rows.Count, 1).End(xlUp).Row