user-determined adding of cell values in a column: code breaks down after 2 cells

[simz92]

Hi everyone,

I have coded a Macro where a user can decide which cell values belong together, these values should then be added in the next column. A maximum of 5 cells should be added together. The user should use characters in the second column to indicate which values belong together. If there is no indication, the original value of the left column should be just copied to the right column. Somehow, the code only recognizes only groups of 2- maybe someone can help?

Here is how it looks like when it breaks down:

Please Login or Register  to view this content.

Here is the code:

Please Login or Register  to view this content.

Thank you so much for your help!

[buran]

The chain comparison for 3-5 processes don't work as you expect. you need to rewrite it.

[simz92]

Hi Buran,

thanks for your reply! I am fairly new to VBA, may I ask how I can compare more than one cell to check whether they have the same value?

[buran]

the most basic change would be

Please Login or Register  to view this content.

There are better ways too, but if you lack VBA experience this will do

[jindon]

Formula in C2
=IF(B2<>"",IF(COUNTIF(B$2:B2,B2)=1,SUMIF($B$2:$B$17,B2,$A$2:$A$17),""),A2)

VBA

Please Login or Register  to view this content.

[simz92]

Thanks Buran, i just thought of the same change, too! :D

Since I want to learn a bit more about vba, i am curious what more ways there are to have the code more efficient? If you have time, i would really appreciate some new knowledge!

Edit: oh i will first check what jindon has just submitted!

[mikerickson]

If you have headers in row 1, put

=IF(B2="",A2,IF(COUNTIF(B$1:B2,B2)=1,COUNTIF(A:A,B2),"")) in C2 and drag down.