Forum Discussion

Abhinav Gupta's avatar
Abhinav Gupta
Copper Contributor
Sep 02, 2018
Solved

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

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 

Macros and VBA
  • Damien_Rosario's avatar
    Damien_Rosario
    Sep 03, 2018

    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

7 Replies

Sort By
  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Sep 03, 2018

    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

    • Abhinav Gupta's avatar
      Abhinav Gupta
      Copper Contributor
      Sep 03, 2018
      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.

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Sep 05, 2018

        Hi Abhinav

         

        Can you give me an example please?

         

        Cheers

        Damien

Resources