turn cells at the end

[zak.horrocks]

replace 9 with 8 start - Copy.xlsm

I attach my workbook here.

I need to distinguish between not reached and omitted i.e. if a candidate answers questions 1,2,4 and 5 out of 15, I want to code question 3 (the blank cell) as Z (omitted) but code questions 6-15 as Y (not reached). The code below works for doing this when omit is 9 and not reached as 8, but when I change 9 to Z and 8 to Y it falls over. Any ideas why or how to get it to work? Thanks.

Please Login or Register  to view this content.

Thank you

Z

[TMS]

You can use this formula to locate the last column in the range:

Please Login or Register  to view this content.

And this to count how many are non blank:

Please Login or Register  to view this content.

That should allow you to determine if all the questions were attempted, if the test was not completed and if there were omissions.

Regards, TMS

[zak.horrocks]

Thanks TMS. I would like to count the Z's in VBA though to populate with a message box. Is this possible?

[TMS]

Ok, this isn't simple, so you need to make a decision.

There are, effectively, four states:
Not started
Started, not finished, gaps
Started, finished, gaps
Started, finished, no gaps

I can give you three separate formulae which will indicate the states, however, combining them would make for a very complex and unmanageable formula. And, if it were repeated many times, it might have an adverse effect on performance.

My recommendation would be to have three separate columns and filter on them as required:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The last formula actually highlights gaps at the end so, in itself, it isn't necessarily a true reputation, as a gap of one or more cells at the end would imply "not finished". So, you can see where I'm going with this.

Happy to provide code to put those three formulae into code if you're happy with that as a compromise.

Regards, TMS