CODEWITHSUNDEEP
excelvba
Ad-vic
Ad-vic

Reputation: 529

calculate count of certain cells using vba in excel

I have build a quiz and displaying score using some checks and output cells have "1" if ans correct and "0" if wrong.

to calculate score I use the function 

=COUNTIF(S7:S158,1)

Problem is the score gets updated as soon user starts writing ans.

I want to have a Macro SUBMIT(using form control) which when clicked gives the score.

what should i write in Sub SUBMIT() so as to execute the above function

Upvotes: 0

Views: 198

Answers (3)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

You can write fomula:

=IF(COUNTA(S7:S158)=x;COUNTIF(S7:S158,1);"")

where x is a number of questions (for example 100). So, function COUNTA counts number of non empty cells in the range, and if the user ansered on all questions, condition COUNTA(S7:S158)=x becames true and the COUNTIF(S7:S158,1) calculated, otherwise an empty string is displayed

Upvotes: 1

Robert Co
Robert Co

Reputation: 1715

I'm a developer and I resort to macro only when all options are exhausted. This is mainly for the benefit of people that might want use/reuse it for other purposes. But that's just my preference.

You can have a cell with a label of Show Score and accepts Yes/No and put an IF around your =COUNTIF(S7:S158,1).

Upvotes: 0

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

You can set the following code in Workbook_Open

Sheet1.EnableCalculation = False

In Sub Submit()

Sheet1.EnableCalculation = True
Sheet1.Calculate

For more, see this

Upvotes: 0

Related Questions