Display the Sum(on click) of ones in the four variables(label) -- one variable is string

[Abhinav Gupta]

Hello sir,

I have a small problem set here attached the project and code.

I am taking answers in the form of YES, NO and NA in the combo drop down. Based on these selections, I am storing values(YES=1, NO=0, NA="NA") in the labels against it. On click over the command button, i want the sum to display in the label option against it. Because the NA data type is different it is not doing the sum. I would appreciate if you provide us the sum work around sum/count the yes events.

Thanks

Abhinav 

[#2]

Please anyone help.

[#3 · Accepted solution]

Hi @Abhinav Gupta

You will need to do two things, (1) Catch the errors so that the NA doesn't kill your code, (2) convert the captions in your labels to integer.

Give this code a try in your CommandButton1:

Private Sub CommandButton1_Click()

'Added labels 1 to 4 to hold the captions from your lbls as integers

Dim Total As Integer
Dim label1 As Integer
Dim label2 As Integer
Dim label3 As Integer
Dim label4 As Integer

'Catches any errors and resumes the code

On Error Resume Next

'Converts your captions (text) into integer (numbers) so it can be summed

label1 = lbl1.Caption
label2 = lbl2.Caption
label3 = lbl3.Caption
label4 = lbl4.Caption

'Sum the values

Total = label1 + label2 + label3 + label4

'Output result
totalqs.Caption = Total

I have added your file with the working code for you to try.

Hope that makes sense and helps!

Cheers

Damien

[#4]

Hi Damien,

Thank for your reply and educating me about the importance of data type and catching errors. That really helped me a lot.
Is there any workaround to Count the Non "NA" value mean count 1 & 0 in the above four variables say? I need to make calculations based on these data to make a ratio.

[#5]

Hi Abhinav

Can you give me an example please?

Cheers

Damien

[#6]

Hi Damien,
Here is an example,
Four Question- Four Answers.
An answer can be YES, NO or NA.
Score = Number of Yes/(Total Number of YES + Total Number of NO)(Excluding all NA option).

So we do not want to count the NA while doing these calculations.

Does that make sense?

Thanks
Abhinav

[#7]

Hi Damien,
Hope you are doing good!
I think I figured out the solutions myself. Thank you for the help.

Cheers
Abhinav

[#8]

Hi @Abhinav Gupta

Sorry I didn't get back to you in time. Have been flat out with running training for go-live of our new financial system next week at work!

Glad you have resolved the question my friend. The fun part of programming is playing around and trying different things to see what works and what doesn't.

Keep up the good work and see you out there!

Cheers

Damien