calculate values for displaying in form

D

dannel

New Member
Joined
Jun 10, 2009
Messages
15
I am trying to populate vales in text boxes on a userform based on different conditions which are linked to combo boxes in the same userform.

After i write the same procedure for multiple conditions i get the error " Procedure too long.

I tried to write the code in a separate procedre and call it after every condition, however it doesn't calculate based on the selected values in the combo box.

HTML: 
Private Sub Cmd_calc_Click()
Dim Masht As Worksheet
Dim i As Integer
    Dim cbx1, cbx2, cbx3, cbx4, cbx5, cbx6 As String
     
J1 = 0
F1 = 0
M1 = 0
A1 = 0
M2 = 0
J2 = 0
J3 = 0
A2 = 0
S1 = 0
O1 = 0
N1 = 0
D1 = 0
    
    cbx1 = Fr_P.cmb_Region.Value
    cbx2 = Fr_P.Cmb_Entity.Value
    cbx3 = Fr_P.Cmb_City.Value
    cbx4 = Fr_P.Cmb_Prop.Value
    cbx5 = Fr_P.Cmb_ExpsHd.Value
    cbx6 = Fr_P.Cmb_ExpsCate.Value
    
  For i = 2 To Masht.Range("A1").End(xlDown).Row
If cbx1 = "All Regions" And cbx2 = Empty And cbx3 = Empty And cbx4 = Empty And cbx5 = Empty And cbx6 = Empty Then
 
Jan = Masht.Range("G" & i).Value
        Janb = Masht.Range("T" & i).Value
        J1 = J1 + Jan
        JB1 = JB1 + Janb
        Feb = Masht.Range("H" & i).Value
        Febb = Masht.Range("U" & i).Value
        F1 = F1 + Feb
        FB1 = FB1 + Febb
        Mar = Masht.Range("I" & i).Value
        Marb = Masht.Range("V" & i).Value
        M1 = M1 + Mar
        MB1 = MB1 + Marb
        Apr = Masht.Range("J" & i).Value
        Aprb = Masht.Range("W" & i).Value
        A1 = A1 + Apr
        AB1 = AB1 + Aprb
        May = Masht.Range("K" & i).Value
        Mayb = Masht.Range("X" & i).Value
        M2 = M2 + May
        MB2 = MB2 + Mayb
        Jun = Masht.Range("L" & i).Value
        Junb = Masht.Range("Y" & i).Value
        J2 = J2 + Jun
        JB2 = JB2 + Junb
        Jul = Masht.Range("M" & i).Value
        Julb = Masht.Range("Z" & i).Value
        J3 = J3 + Jul
        JB3 = JB3 + Julb
        Aug = Masht.Range("N" & i).Value
        Augb = Masht.Range("AA" & i).Value
        A2 = A2 + Aug
        AB2 = AB2 + Augb
        Sep = Masht.Range("O" & i).Value
        Sepb = Masht.Range("AB" & i).Value
        S1 = S1 + Sep
        SB1 = SB1 + Sepb
        Oct = Masht.Range("P" & i).Value
        Octb = Masht.Range("AC" & i).Value
        O1 = O1 + Oct
        OB1 = OB1 + Octb
        Nov = Masht.Range("Q" & i).Value
        Novb = Masht.Range("AD" & i).Value
        N1 = N1 + Nov
        NB1 = NB1 + Novb
        Dec = Masht.Range("R" & i).Value
        Decb = Masht.Range("AE" & i).Value
        D1 = D1 + Dec
        DB1 = DB1 + Decb
        
        
        Jan = Masht.Range("G" & i).Value
        Janb = Masht.Range("T" & i).Value
        J1 = J1 + Jan
        JB1 = JB1 + Janb
        Feb = Masht.Range("H" & i).Value
        Febb = Masht.Range("U" & i).Value
        F1 = F1 + Feb
        FB1 = FB1 + Febb
        Mar = Masht.Range("I" & i).Value
        Marb = Masht.Range("V" & i).Value
        M1 = M1 + Mar
        MB1 = MB1 + Marb
        Apr = Masht.Range("J" & i).Value
        Aprb = Masht.Range("W" & i).Value
        A1 = A1 + Apr
        AB1 = AB1 + Aprb
        May = Masht.Range("K" & i).Value
        Mayb = Masht.Range("X" & i).Value
        M2 = M2 + May
        MB2 = MB2 + Mayb
        Jun = Masht.Range("L" & i).Value
        Junb = Masht.Range("Y" & i).Value
        J2 = J2 + Jun
        JB2 = JB2 + Junb
        Jul = Masht.Range("M" & i).Value
        Julb = Masht.Range("Z" & i).Value
        J3 = J3 + Jul
        JB3 = JB3 + Julb
        Aug = Masht.Range("N" & i).Value
        Augb = Masht.Range("AA" & i).Value
        A2 = A2 + Aug
        AB2 = AB2 + Augb
        Sep = Masht.Range("O" & i).Value
        Sepb = Masht.Range("AB" & i).Value
        S1 = S1 + Sep
        SB1 = SB1 + Sepb
        Oct = Masht.Range("P" & i).Value
        Octb = Masht.Range("AC" & i).Value
        O1 = O1 + Oct
        OB1 = OB1 + Octb
        Nov = Masht.Range("Q" & i).Value
        Novb = Masht.Range("AD" & i).Value
        N1 = N1 + Nov
        NB1 = NB1 + Novb
        Dec = Masht.Range("R" & i).Value
        Decb = Masht.Range("AE" & i).Value
        D1 = D1 + Dec
        DB1 = DB1 + Decb
        
        ElseIf cbx1 = "Masht.Range("A" & i).Value" And cbx2 = Empty And cbx3 = Empty And cbx4 = Empty And cbx5 = Empty And cbx6 = Masht.Range("E" & i).Value Then
 
        Jan = Masht.Range("G" & i).Value
        Janb = Masht.Range("T" & i).Value
        J1 = J1 + Jan
        JB1 = JB1 + Janb
        Feb = Masht.Range("H" & i).Value
        Febb = Masht.Range("U" & i).Value
        F1 = F1 + Feb
        FB1 = FB1 + Febb
        Mar = Masht.Range("I" & i).Value
        Marb = Masht.Range("V" & i).Value
        M1 = M1 + Mar
        MB1 = MB1 + Marb
        Apr = Masht.Range("J" & i).Value
        Aprb = Masht.Range("W" & i).Value
        A1 = A1 + Apr
        AB1 = AB1 + Aprb
        May = Masht.Range("K" & i).Value
        Mayb = Masht.Range("X" & i).Value
        M2 = M2 + May
        MB2 = MB2 + Mayb
        Jun = Masht.Range("L" & i).Value
        Junb = Masht.Range("Y" & i).Value
        J2 = J2 + Jun
        JB2 = JB2 + Junb
        Jul = Masht.Range("M" & i).Value
        Julb = Masht.Range("Z" & i).Value
        J3 = J3 + Jul
        JB3 = JB3 + Julb
        Aug = Masht.Range("N" & i).Value
        Augb = Masht.Range("AA" & i).Value
        A2 = A2 + Aug
        AB2 = AB2 + Augb
        Sep = Masht.Range("O" & i).Value
        Sepb = Masht.Range("AB" & i).Value
        S1 = S1 + Sep
        SB1 = SB1 + Sepb
        Oct = Masht.Range("P" & i).Value
        Octb = Masht.Range("AC" & i).Value
        O1 = O1 + Oct
        OB1 = OB1 + Octb
        Nov = Masht.Range("Q" & i).Value
        Novb = Masht.Range("AD" & i).Value
        N1 = N1 + Nov
        NB1 = NB1 + Novb
        Dec = Masht.Range("R" & i).Value
        Decb = Masht.Range("AE" & i).Value
        D1 = D1 + Dec
        DB1 = DB1 + Decb
        
        
        Jan = Masht.Range("G" & i).Value
        Janb = Masht.Range("T" & i).Value
        J1 = J1 + Jan
        JB1 = JB1 + Janb
        Feb = Masht.Range("H" & i).Value
        Febb = Masht.Range("U" & i).Value
        F1 = F1 + Feb
        FB1 = FB1 + Febb
        Mar = Masht.Range("I" & i).Value
        Marb = Masht.Range("V" & i).Value
        M1 = M1 + Mar
        MB1 = MB1 + Marb
        Apr = Masht.Range("J" & i).Value
        Aprb = Masht.Range("W" & i).Value
        A1 = A1 + Apr
        AB1 = AB1 + Aprb
        May = Masht.Range("K" & i).Value
        Mayb = Masht.Range("X" & i).Value
        M2 = M2 + May
        MB2 = MB2 + Mayb
        Jun = Masht.Range("L" & i).Value
        Junb = Masht.Range("Y" & i).Value
        J2 = J2 + Jun
        JB2 = JB2 + Junb
        Jul = Masht.Range("M" & i).Value
        Julb = Masht.Range("Z" & i).Value
        J3 = J3 + Jul
        JB3 = JB3 + Julb
        Aug = Masht.Range("N" & i).Value
        Augb = Masht.Range("AA" & i).Value
        A2 = A2 + Aug
        AB2 = AB2 + Augb
        Sep = Masht.Range("O" & i).Value
        Sepb = Masht.Range("AB" & i).Value
        S1 = S1 + Sep
        SB1 = SB1 + Sepb
        Oct = Masht.Range("P" & i).Value
        Octb = Masht.Range("AC" & i).Value
        O1 = O1 + Oct
        OB1 = OB1 + Octb
        Nov = Masht.Range("Q" & i).Value
        Novb = Masht.Range("AD" & i).Value
        N1 = N1 + Nov
        NB1 = NB1 + Novb
        Dec = Masht.Range("R" & i).Value
        Decb = Masht.Range("AE" & i).Value
        D1 = D1 + Dec
        DB1 = DB1 + Decb
 
      Else
                
  End If
    
Next i
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi Dannel,

A few suggestions to tighten up your code.

When declaring variables, declare the data type. For example, declaring variables as such:

Dim cbx1, cbx2, cbx3, cbx4, cbx5, cbx6 As String
Click to expand...
Means that you have five variables of data type variant, and one of data type string. If they are all meant to be string variables declare like this.

Code: 
  [COLOR=darkblue]Dim[/COLOR] cbx1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx3 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] cbx4 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx5 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx6 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
You have declared a worksheet variable:
Code: 
  [COLOR=darkblue]Dim[/COLOR] Masht [COLOR=darkblue]As[/COLOR] Worksheet
Before you can use this variable in code you have to set it up: Change "Sheet1" to your worksheet name.

Code: 
  [COLOR=darkblue]Set[/COLOR] Masht = Worksheets("[COLOR=Red]Sheet1[/COLOR]")
     
  [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] Masht.Range("A1").End(xlDown).Row
You have a lot of superflous calculations in your code. For example, this:

Code: 
Jan = Masht.Range("G" & i).Value 
Janb = Masht.Range("T" & i).Value
J1 = J1 + Jan
JB1 = JB1 + Janb

 Feb = Masht.Range("H" & i).Value
 Febb = Masht.Range("U" & i).Value
 F1 = F1 + Feb
 FB1 = FB1 + Febb

etc
Can be replaced with something like:

Code: 
          J1 = J1 + .Range("G" & i).Value
          JB1 = JB1 + .Range("T" & i).Value
          F1 = F1 + .Range("H" & i).Value
          FB1 = FB1 + .Range("U" & i).Value
      
        [COLOR=green]'remainder goes here[/COLOR]
NB numeric variables are zero by default on initialzation.

In your ElseIf statement remove the double quotations:

ElseIf cbx1 = "Masht.Range("A" & i).Value"
Click to expand...
So to summarise, here is how I would layout your code:
Note the use of the With...End With statement.

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Cmd_calc_Click()
  [COLOR=darkblue]Dim[/COLOR] Masht [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] cbx1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx3 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] cbx4 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx5 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cbx6 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
     
  [COLOR=green]'I have assumed that these variables take the value of the combo boxes.[/COLOR]
  cbx1 = Fr_P.cmb_Region.Value
  cbx2 = Fr_P.Cmb_Entity.Value
  cbx3 = Fr_P.Cmb_City.Value
  cbx4 = Fr_P.Cmb_Prop.Value
  cbx5 = Fr_P.Cmb_ExpsHd.Value
  cbx6 = Fr_P.Cmb_ExpsCate.Value
    
  [COLOR=darkblue]Set[/COLOR] Masht = Worksheets("Sheet1")
     
  [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] Masht.Range("A1").End(xlDown).Row

    [COLOR=darkblue]With[/COLOR] Masht
      [COLOR=darkblue]If[/COLOR] cbx1 = "All Regions" And _
         cbx2 = [COLOR=darkblue]Empty[/COLOR] And _
         cbx3 = [COLOR=darkblue]Empty[/COLOR] And _
         cbx4 = [COLOR=darkblue]Empty[/COLOR] And _
         cbx5 = [COLOR=darkblue]Empty[/COLOR] And _
         cbx6 = [COLOR=darkblue]Empty[/COLOR] [COLOR=darkblue]Then[/COLOR]
       
          J1 = J1 + .Range("G" & i).Value
          JB1 = JB1 + .Range("T" & i).Value
          F1 = F1 + .Range("H" & i).Value
          FB1 = FB1 + .Range("U" & i).Value
      
        [COLOR=green]'remainder goes here[/COLOR]
      
      
      [COLOR=darkblue]ElseIf[/COLOR] cbx1 = .Range("A" & i).Value And _
             cbx2 = [COLOR=darkblue]Empty[/COLOR] And _
             cbx3 = [COLOR=darkblue]Empty[/COLOR] And _
             cbx4 = [COLOR=darkblue]Empty[/COLOR] And _
             cbx5 = [COLOR=darkblue]Empty[/COLOR] And _
             cbx6 = .Range("E" & i).Value [COLOR=darkblue]Then[/COLOR]
         
          J1 = J1 + .Range("G" & i).Value
          JB1 = JB1 + .Range("T" & i).Value
          F1 = F1 + .Range("H" & i).Value
          FB1 = FB1 + .Range("U" & i).Value
     
      [COLOR=green]'remainder goes here[/COLOR]
    
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Substituting Values from a list
2
Replies
19
Views
1K
jindon
J

Forum statistics

Threads
1,224,532
Messages
6,179,388
Members
452,908
Latest member
MTDelphis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top