The code for sum in column 'an' not working

[mukeshbaviskar]

Hi friends,
In my file the code for column 'an' is not working. It's a total marks. The code is in sheet1.

Regards,

Mukesh

[sktneer]

I couldn't understand when you said not working. Is is worksheet change event which is not working or the formula is not working? Because when I re-entered the values in col. G which were already entered by you in the uploaded sheet. The col. AN populated with the sum. I think you have an issue with the worksheet change event not with the formula because in your uploaded sheet, I found values in col. G but no values in col. AN.
I think you need to clarify that.

[mukeshbaviskar]

Hi sktneer,
Thank you for your response. There is no issue with a formula in column 'an' which you suggested. The formula is absolutely correct.

The problem is in worksheet change event. All other codes are working fine except the code for addition in column 'an'.

Thank you.

Regards,

Mukesh

[sktneer]

The code is running perfect at my end. The col. AN is populating with the sum values correctly.
But while checking the code, I also faced this issue when worksheet change event stopped responding. In that case what I did was, I closed the workbook and opened it again and the code started responding again. This behavior is really strange. No clue.

[mukeshbaviskar]

Hi sktneer,
I tried in my file but no output. So I inserted the code in a new file attached here. Please check it and upload it for me again so that if anything is wrong with me will be corrected from your end.

Sorry and thank you.

Regards,

Mukesh

[sktneer]

The worksheet change event is responding as I entered few values in the sheet. I entered the formula itself in AP8 to compare the result generated in AN8 and found that if I enter "A" in S8, AN8 becomes blank while AP8 shows the correct value as per the formula.
So I think there is an issue in applying the formula in VBA. I am not good enough to find out the bug. I can understand your frustration.
Maybe someone who is expert in VBA coding can help you. This forum is full of such experts. lets hope if someone comes forward to help.

[mukeshbaviskar]

Hi sktneer,
Now I got the problem. The file may be corrupted so I created a new file and the code is working fine as per requirement.

If there is 'A' in all columns or in 2 adjacent column (d,e; h,i; l,m; p,q; and so on)the formula in column 'an' gives a wrong output. I inserted a formula in column 'ap' where I compare my output but it's wrong.

It's taking a little more time to work because of array formula takes a little more time to work. Please don't mind. Do the necessary correction in formula for column 'an' then the problem will be solve.

Thank you and good night.

Regards,

Mukesh

[sktneer]

Try this to see if this works...........

Please Login or Register  to view this content.

[mukeshbaviskar]

Hi sktneer,
Good morning!

Excellent! Now it's working fine with correct output.

I appreciate you. Thank you.

Regards,

Mukesh

[sktneer]

Did you check that what sum you get in AN8 when you input "A" in S8 with the new formula?

[mukeshbaviskar]

Hello sktneer,
There is no need to input the any value in 's8' because it contains a formula. I tested the formula by changing values in the row in different ways and got correct output with your new formula.

Thank you for taking trouble and having a deep concern about my problem to solve it finally.

Thank you.

Have a nice day!

Regards,

Mukesh

[sktneer]

Glad to know that your issue has been resolved.

Take Care....

*** Don't forget to mark your thread as solved.

[sktneer]

I am still getting sum in col. AN if I put "A" in any of the columns you mentioned ('d,e; h,i; l,m; p,q; t,u; x,y; ab,ac; af,ag; aj,ak'). See the attached.

[sktneer]

Try to change the formula to ...........

Please Login or Register  to view this content.

And see what happens?

[sktneer]

And moreover if you put "A" in all the columns ('d,e; h,i; l,m; p,q; t,u; x,y; ab,ac; af,ag; aj,ak'), the formula still gives the correct sum which is 1 but the code doesn't. The problem what I see is that the formula is not working, as it should, when it is put in the code.

[sktneer]

I think all is well now. Please see the attached sheet.

[mukeshbaviskar]

Hi sktneer,
Excellent! Excellent! Excellent!

After all we achieve the target. Credit goes to you because of your sincere hard work we achieve the target which is very critical.

The file attached in post 16 is very perfect with correct output.

Thank you for your kind efforts to solve my problem.

Regards,

Mukesh

[sktneer]

Yes. Glad that after all it worked. Did you notice the change in the code? This was all because of few unnecessary lines of codes which I removed.
I also put the latest formula in the code as well.

Hope that you don't have any issue with this now.

Take Care....