Procedure too large

R

r4944

New Member
Joined
Apr 26, 2016
Messages
5
Hi There ,

I would like to show the definition of each definition placed in a combo box , I use more over 80 drop down list

here is my code ; it was working good but when i add some new definition i got this error (Procedure too large )


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address(0, 0) = "AA4" Then
    
    Select Case Target
        Case "EDU1":  range1 = "B19"
        Case "EDU2":  range1 = "B20"
        Case "EDU3":  range1 = "B21"
        Case "EDU4":  range1 = "B22"
        Case "EXP1":  range1 = "B25"
        Case "EXP2":  range1 = "B26"
        Case "EXP3":  range1 = "B27"
        Case "EXP4":  range1 = "B28"
        Case "EXP5":  range1 = "B29"
        Case "EXP6":  range1 = "B30"
        Case "EXP7":  range1 = "B31"
        Case "EXP8":  range1 = "B32"
        Case "EXP9":  range1 = "B33"
        Case "EXP10": range1 = "B34"
        Case "EXP11": range1 = "B35"
        Case "EXP12": range1 = "B36"
        Case "EXP13": range1 = "B37"
        Case "EXP14": range1 = "B38"
        Case "EXP15": range1 = "B39"
        Case "EXP16": range1 = "B40"
        Case "EXP17": range1 = "B41"
        Case "EXP18": range1 = "B42"
        Case "EXP19": range1 = "B43"
        Case "EXP20": range1 = "B44"
        Case "A1": range1 = "B71"
        Case "A2": range1 = "B72"
        Case "PS1": range1 = "B83"
        Case "Comp1": range1 = "B95"
        Case "Comp2": range1 = "B96"
        Case "AEDU1": range1 = "B116"
        Case "AEDU2": range1 = "B117"
        Case "AEXP1": range1 = "B121"
        Case "AEXP2": range1 = "B122"
        Case "AEXP3": range1 = "B123"
     
     

     End Select
End If

If range1 <> "" Then
     Sheets("SBR").Range("AA3") = _
                Sheets("Process Info").Range(range1).Value
End If
       
  If Target.Address(0, 0) = "AB4" Then
    
    Select Case Target
        Case "EDU1":  rangeX = "B19"
        Case "EDU2":  rangeX = "B20"
        Case "EDU3":  rangeX = "B21"
        Case "EDU4":  rangeX = "B22"
        Case "EXP1":  rangeX = "B25"
        Case "EXP2":  rangeX = "B26"
        Case "EXP3":  rangeX = "B27"
        Case "EXP4":  rangeX = "B28"
        Case "EXP5":  rangeX = "B29"
        Case "EXP6":  rangeX = "B30"
        Case "EXP7":  rangeX = "B31"
        Case "EXP8":  rangeX = "B32"
        Case "EXP9":  rangeX = "B33"
        Case "EXP10": rangeX = "B34"
        Case "EXP11": rangeX = "B35"
        Case "EXP12": rangeX = "B36"
        Case "EXP13": rangeX = "B37"
        Case "EXP14": rangeX = "B38"
        Case "EXP15": rangeX = "B39"
        Case "EXP16": rangeX = "B40"
        Case "EXP17": rangeX = "B41"
        Case "EXP18": rangeX = "B42"
        Case "EXP19": rangeX = "B43"
        Case "EXP20": rangeX = "B44"
        Case "A1": rangeX = "B71"
        Case "A2": rangeX = "B72"
        Case "PS1": rangeX = "B83"
        Case "Comp1": rangeX = "B95"
        Case "Comp2": rangeX = "B96"
        Case "AEDU1": rangeX = "B116"
        Case "AEDU2": rangeX = "B117"
        Case "AEXP1": rangeX = "B121"
        Case "AEXP2": rangeX = "B122"
        Case "AEXP3": rangeX = "B123"
     
     

     End Select
End If

If rangeX <> "" Then
     Sheets("SBR").Range("AB3") = _
                Sheets("Process Info").Range(rangeX).Value
End If
            
       
       
       
       

       
       
     
If Target.Address(0, 0) = "AC4" Then
    
    Select Case Target
       Case "EDU1":  range1a = "B19"
        Case "EDU2":  range1a = "B20"
        Case "EDU3":  range1a = "B21"
        Case "EDU4":  range1a = "B22"
        Case "EXP1":  range1a = "B25"
        Case "EXP2":  range1a = "B26"
        Case "EXP3":  range1a = "B27"
        Case "EXP4":  range1a = "B28"
        Case "EXP5":  range1a = "B29"
        Case "EXP6":  range1a = "B30"
        Case "EXP7":  range1a = "B31"
        Case "EXP8":  range1a = "B32"
        Case "EXP9":  range1a = "B33"
        Case "EXP10": range1a = "B34"
        Case "EXP11": range1a = "B35"
        Case "EXP12": range1a = "B36"
        Case "EXP13": range1a = "B37"
        Case "EXP14": range1a = "B38"
        Case "EXP15": range1a = "B39"
        Case "EXP16": range1a = "B40"
        Case "EXP17": range1a = "B41"
        Case "EXP18": range1a = "B42"
        Case "EXP19": range1a = "B43"
        Case "EXP20": range1a = "B44"
        Case "A1": range1a = "B71"
        Case "A2": range1a = "B72"
        Case "PS1": range1a = "B83"
        Case "Comp1": range1a = "B95"
        Case "Comp2": range1a = "B96"
        Case "AEDU1": range1a = "B116"
        Case "AEDU2": range1a = "B117"
        Case "AEXP1": range1a = "B121"
        Case "AEXP2": range1a = "B122"
        Case "AEXP3": range1a = "B123"
     
     End Select
End If

If range1a <> "" Then
     Sheets("SBR").Range("AC3") = _
                Sheets("Process Info").Range(range1a).Value
End If
       
     

If Target.Address(0, 0) = "Z4" Then
    
    Select Case Target
              Case "EDU1":  range1a = "B19"
        Case "EDU2":  range1b = "B20"
        Case "EDU3":  range1b = "B21"
        Case "EDU4":  range1b = "B22"
        Case "EXP1":  range1b = "B25"
        Case "EXP2":  range1b = "B26"
        Case "EXP3":  range1b = "B27"
        Case "EXP4":  range1b = "B28"
        Case "EXP5":  range1b = "B29"
        Case "EXP6":  range1b = "B30"
        Case "EXP7":  range1b = "B31"
        Case "EXP8":  range1b = "B32"
        Case "EXP9":  range1b = "B33"
        Case "EXP10": range1b = "B34"
        Case "EXP11": range1b = "B35"
        Case "EXP12": range1b = "B36"
        Case "EXP13": range1b = "B37"
        Case "EXP14": range1b = "B38"
        Case "EXP15": range1b = "B39"
        Case "EXP16": range1b = "B40"
        Case "EXP17": range1b = "B41"
        Case "EXP18": range1b = "B42"
        Case "EXP19": range1b = "B43"
        Case "EXP20": range1b = "B44"
        Case "A1": range1b = "B71"
        Case "A2": range1b = "B72"
        Case "PS1": range1b = "B83"
        Case "Comp1": range1b = "B95"
        Case "Comp2": range1b = "B96"
        Case "AEDU1": range1b = "B116"
        Case "AEDU2": range1b = "B117"
        Case "AEXP1": range1b = "B121"
        Case "AEXP2": range1b = "B122"
        Case "AEXP3": range1b = "B123"
     
     End Select
End If

If range1b <> "" Then
     Sheets("SBR").Range("Z3") = _
                Sheets("Process Info").Range(range1b).Value
End If
       
    

If Target.Address(0, 0) = "AD4" Then
    
    Select Case Target
        Case "EDU1":  range1c = "B19"
        Case "EDU2":  range1c = "B20"
        Case "EDU3":  range1c = "B21"
        Case "EDU4":  range1c = "B22"
        Case "EXP1":  range1c = "B25"
        Case "EXP2":  range1c = "B26"
        Case "EXP3":  range1c = "B27"
        Case "EXP4":  range1c = "B28"
        Case "EXP5":  range1c = "B29"
        Case "EXP6":  range1c = "B30"
        Case "EXP7":  range1c = "B31"
        Case "EXP8":  range1c = "B32"
        Case "EXP9":  range1c = "B33"
        Case "EXP10": range1c = "B34"
        Case "EXP11": range1c = "B35"
        Case "EXP12": range1c = "B36"
        Case "EXP13": range1c = "B37"
        Case "EXP14": range1c = "B38"
        Case "EXP15": range1c = "B39"
        Case "EXP16": range1c = "B40"
        Case "EXP17": range1c = "B41"
        Case "EXP18": range1c = "B42"
        Case "EXP19": range1c = "B43"
        Case "EXP20": range1c = "B44"
        Case "A1": range1c = "B71"
        Case "A2": range1c = "B72"
        Case "PS1": range1c = "B83"
        Case "Comp1": range1c = "B95"
        Case "Comp2": range1c = "B96"
        Case "AEDU1": range1c = "B116"
        Case "AEDU2": range1c = "B117"
        Case "AEXP1": range1c = "B121"
        Case "AEXP2": range1c = "B122"
        Case "AEXP3": range1c = "B123"
     
     End Select
End If

If range1c <> "" Then
     Sheets("SBR").Range("AD3") = _
                Sheets("Process Info").Range(range1c).Value
End If
       
     
    
     
     
     
     
If Target.Address(0, 0) = "AE4" Then
    
    Select Case Target
        Case "EDU1":  range1x = "B19"
        Case "EDU2":  range1x = "B20"
        Case "EDU3":  range1x = "B21"
        Case "EDU4":  range1x = "B22"
        Case "EXP1":  range1x = "B25"
        Case "EXP2":  range1x = "B26"
        Case "EXP3":  range1x = "B27"
        Case "EXP4":  range1x = "B28"
        Case "EXP5":  range1x = "B29"
        Case "EXP6":  range1x = "B30"
        Case "EXP7":  range1x = "B31"
        Case "EXP8":  range1x = "B32"
        Case "EXP9":  range1x = "B33"
        Case "EXP10": range1x = "B34"
        Case "EXP11": range1x = "B35"
        Case "EXP12": range1x = "B36"
        Case "EXP13": range1x = "B37"
        Case "EXP14": range1x = "B38"
        Case "EXP15": range1x = "B39"
        Case "EXP16": range1x = "B40"
        Case "EXP17": range1x = "B41"
        Case "EXP18": range1x = "B42"
        Case "EXP19": range1x = "B43"
        Case "EXP20": range1x = "B44"
        Case "A1": range1x = "B71"
        Case "A2": range1x = "B72"
        Case "PS1": range1x = "B83"
        Case "Comp1": range1x = "B95"
        Case "Comp2": range1x = "B96"
        Case "AEDU1": range1x = "B116"
        Case "AEDU2": range1x = "B117"
        Case "AEXP1": range1x = "B121"
        Case "AEXP2": range1x = "B122"
        Case "AEXP3": range1x = "B123"
     
     End Select
End If

If range1x <> "" Then
     Sheets("SBR").Range("AE3") = _
                Sheets("Process Info").Range(range1x).Value
End If
       
     

       
     


If Target.Address(0, 0) = "AF4" Then
    
    Select Case Target
        Case "EDU1":  range1s = "B19"
        Case "EDU2":  range1s = "B20"
        Case "EDU3":  range1s = "B21"
        Case "EDU4":  range1s = "B22"
        Case "EXP1":  range1s = "B25"
        Case "EXP2":  range1s = "B26"
        Case "EXP3":  range1s = "B27"
        Case "EXP4":  range1s = "B28"
        Case "EXP5":  range1s = "B29"
        Case "EXP6":  range1s = "B30"
        Case "EXP7":  range1s = "B31"
        Case "EXP8":  range1s = "B32"
        Case "EXP9":  range1s = "B33"
        Case "EXP10": range1s = "B34"
        Case "EXP11": range1s = "B35"
        Case "EXP12": range1s = "B36"
        Case "EXP13": range1s = "B37"
        Case "EXP14": range1s = "B38"
        Case "EXP15": range1s = "B39"
        Case "EXP16": range1s = "B40"
        Case "EXP17": range1s = "B41"
        Case "EXP18": range1s = "B42"
        Case "EXP19": ranges1s = "B43"
        Case "EXP20": range1s = "B44"
        Case "A1": range1s = "B71"
        Case "A2": range1s = "B72"
        Case "PS1": range1s = "B83"
        Case "Comp1": range1s = "B95"
        Case "Comp2": range1s = "B96"
        Case "AEDU1": range1s = "B116"
        Case "AEDU2": range1s = "B117"
        Case "AEXP1": range1s = "B121"
        Case "AEXP2": range1s = "B122"
        Case "AEXP3": range1s = "B123"
     
     End Select
End If

If range1s <> "" Then
     Sheets("SBR").Range("AF3") = _
                Sheets("Process Info").Range(range1s).Value
End If
       
     

If Target.Address(0, 0) = "AG4" Then
    
    Select Case Target
         Case "EDU1":  range1q = "B19"
        Case "EDU2":  range1q = "B20"
        Case "EDU3":  range1q = "B21"
        Case "EDU4":  range1q = "B22"
        Case "EXP1":  range1q = "B25"
        Case "EXP2":  range1q = "B26"
        Case "EXP3":  range1q = "B27"
        Case "EXP4":  range1q = "B28"
        Case "EXP5":  range1q = "B29"
        Case "EXP6":  range1q = "B30"
        Case "EXP7":  range1q = "B31"
        Case "EXP8":  range1q = "B32"
        Case "EXP9":  range1q = "B33"
        Case "EXP10": range1q = "B34"
        Case "EXP11": range1q = "B35"
        Case "EXP12": range1q = "B36"
        Case "EXP13": range1q = "B37"
        Case "EXP14": range1q = "B38"
        Case "EXP15": range1q = "B39"
        Case "EXP16": range1q = "B40"
        Case "EXP17": range1q = "B41"
        Case "EXP18": range1q = "B42"
        Case "EXP19": range1q = "B43"
        Case "EXP20": range1q = "B44"
        Case "A1": range1q = "B71"
        Case "A2": range1q = "B72"
        Case "PS1": range1q = "B83"
        Case "Comp1": range1q = "B95"
        Case "Comp2": range1q = "B96"
        Case "AEDU1": range1q = "B116"
        Case "AEDU2": range1q = "B117"
        Case "AEXP1": range1q = "B121"
        Case "AEXP2": range1q = "B122"
        Case "AEXP3": range1q = "B123"
     End Select
End If

If range1q <> "" Then
     Sheets("SBR").Range("AG3") = _
                Sheets("Process Info").Range(range1q).Value
End If
       
     

If Target.Address(0, 0) = "AH4" Then
    
    Select Case Target
       
       
       
       
        Case "EDU1":  range1e = "B19"
        Case "EDU2":  range1e = "B20"
        Case "EDU3":  range1e = "B21"
        Case "EDU4":  range1e = "B22"
        Case "EXP1":  range1e = "B25"
        Case "EXP2":  range1e = "B26"
        Case "EXP3":  range1e = "B27"
        Case "EXP4":  range1e = "B28"
        Case "EXP5":  range1e = "B29"
        Case "EXP6":  range1e = "B30"
        Case "EXP7":  range1e = "B31"
        Case "EXP8":  range1e = "B32"
        Case "EXP9":  range1e = "B33"
        Case "EXP10": range1e = "B34"
        Case "EXP11": range1e = "B35"
        Case "EXP12": range1e = "B36"
        Case "EXP13": range1e = "B37"
        Case "EXP14": range1e = "B38"
        Case "EXP15": range1e = "B39"
        Case "EXP16": range1e = "B40"
        Case "EXP17": range1e = "B41"
        Case "EXP18": range1e = "B42"
        Case "EXP19": range1e = "B43"
        Case "EXP20": range1e = "B44"
        Case "A1":    range1e = "B71"
        Case "A2":    range1e = "B72"
        Case "PS1":   range1e = "B83"
        Case "Comp1": range1e = "B95"
        Case "Comp2": range1e = "B96"
        Case "AEDU1": range1e = "B116"
        Case "AEDU2": range1e = "B117"
        Case "AEXP1": range1e = "B121"
        Case "AEXP2": range1e = "B122"
        Case "AEXP3": range1e = "B123"
       
    
     End Select
End If

If range1e <> "" Then
     Sheets("SBR").Range("AH3") = _
                Sheets("Process Info").Range(range1e).Value
End If
        
        
If Target.Address(0, 0) = "AI4" Then
    
    Select Case Target
      
       Case "EDU1":  range1n = "B19"
        Case "EDU2":  range1n = "B20"
        Case "EDU3":  range1n = "B21"
        Case "EDU4":  range1n = "B22"
        Case "EXP1":  range1n = "B25"
        Case "EXP2":  range1n = "B26"
        Case "EXP3":  range1n = "B27"
        Case "EXP4":  range1n = "B28"
        Case "EXP5":  range1n = "B29"
        Case "EXP6":  range1n = "B30"
        Case "EXP7":  range1n = "B31"
        Case "EXP8":  range1n = "B32"
        Case "EXP9":  range1n = "B33"
        Case "EXP10": range1n = "B34"
        Case "EXP11": range1n = "B35"
        Case "EXP12": range1n = "B36"
        Case "EXP13": range1n = "B37"
        Case "EXP14": range1n = "B38"
        Case "EXP15": range1n = "B39"
        Case "EXP16": range1n = "B40"
        Case "EXP17": range1n = "B41"
        Case "EXP18": range1n = "B42"
        Case "EXP19": range1n = "B43"
        Case "EXP20": range1n = "B44"
        Case "A1": range1n = "B71"
        Case "A2": range1n = "B72"
        Case "PS1": range1n = "B83"
        Case "Comp1": range1n = "B95"
        Case "Comp2": range1n = "B96"
        Case "AEDU1": range1n = "B116"
        Case "AEDU2": range1n = "B117"
        Case "AEXP1": range1n = "B121"
        Case "AEXP2": range1n = "B122"
        Case "AEXP3": range1n = "B123"
      
     End Select
End If

If range1n <> "" Then
     Sheets("SBR").Range("AI3") = _
                Sheets("Process Info").Range(range1n).Value
End If
       
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
end sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
You could store the values in arrays, and search the arrays

Something lke

CaseArray = Array("EDU1", "EDU2", "EDU3", "EDU4", "EXP1", "EXP2")
RsltArray = Array("B19", "B20", "B21", "B22", "B25", "B26")
MyVal = Application.Match(Target, CaseArray, 0) - 1
range1 = RsltArray(MyVal)
 
Upvote 0
r4944 said:
Hi There ,

I would like to show the definition of each definition placed in a combo box , I use more over 80 drop down list

here is my code ; it was working good but when i add some new definition i got this error (Procedure too large )


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address(0, 0) = "AA4" Then
    
    Select Case Target
        Case "EDU1":  range1 = "B19"
        Case "EDU2":  range1 = "B20"
        Case "EDU3":  range1 = "B21"
        Case "EDU4":  range1 = "B22"
        Case "EXP1":  range1 = "B25"
        Case "EXP2":  range1 = "B26"
        Case "EXP3":  range1 = "B27"
        Case "EXP4":  range1 = "B28"
        Case "EXP5":  range1 = "B29"
        Case "EXP6":  range1 = "B30"
        Case "EXP7":  range1 = "B31"
        Case "EXP8":  range1 = "B32"
        Case "EXP9":  range1 = "B33"
        Case "EXP10": range1 = "B34"
        Case "EXP11": range1 = "B35"
        Case "EXP12": range1 = "B36"
        Case "EXP13": range1 = "B37"
        Case "EXP14": range1 = "B38"
        Case "EXP15": range1 = "B39"
        Case "EXP16": range1 = "B40"
        Case "EXP17": range1 = "B41"
        Case "EXP18": range1 = "B42"
        Case "EXP19": range1 = "B43"
        Case "EXP20": range1 = "B44"
        Case "A1": range1 = "B71"
        Case "A2": range1 = "B72"
        Case "PS1": range1 = "B83"
        Case "Comp1": range1 = "B95"
        Case "Comp2": range1 = "B96"
        Case "AEDU1": range1 = "B116"
        Case "AEDU2": range1 = "B117"
        Case "AEXP1": range1 = "B121"
        Case "AEXP2": range1 = "B122"
        Case "AEXP3": range1 = "B123"
     
     

     End Select
End If

If range1 <> "" Then
     Sheets("SBR").Range("AA3") = _
                Sheets("Process Info").Range(range1).Value
End If
       
  If Target.Address(0, 0) = "AB4" Then
    
    Select Case Target
        Case "EDU1":  rangeX = "B19"
        Case "EDU2":  rangeX = "B20"
        Case "EDU3":  rangeX = "B21"
        Case "EDU4":  rangeX = "B22"
        Case "EXP1":  rangeX = "B25"
        Case "EXP2":  rangeX = "B26"
        Case "EXP3":  rangeX = "B27"
        Case "EXP4":  rangeX = "B28"
        Case "EXP5":  rangeX = "B29"
        Case "EXP6":  rangeX = "B30"
        Case "EXP7":  rangeX = "B31"
        Case "EXP8":  rangeX = "B32"
        Case "EXP9":  rangeX = "B33"
        Case "EXP10": rangeX = "B34"
        Case "EXP11": rangeX = "B35"
        Case "EXP12": rangeX = "B36"
        Case "EXP13": rangeX = "B37"
        Case "EXP14": rangeX = "B38"
        Case "EXP15": rangeX = "B39"
        Case "EXP16": rangeX = "B40"
        Case "EXP17": rangeX = "B41"
        Case "EXP18": rangeX = "B42"
        Case "EXP19": rangeX = "B43"
        Case "EXP20": rangeX = "B44"
        Case "A1": rangeX = "B71"
        Case "A2": rangeX = "B72"
        Case "PS1": rangeX = "B83"
        Case "Comp1": rangeX = "B95"
        Case "Comp2": rangeX = "B96"
        Case "AEDU1": rangeX = "B116"
        Case "AEDU2": rangeX = "B117"
        Case "AEXP1": rangeX = "B121"
        Case "AEXP2": rangeX = "B122"
        Case "AEXP3": rangeX = "B123"
     
     

     End Select
End If

If rangeX <> "" Then
     Sheets("SBR").Range("AB3") = _
                Sheets("Process Info").Range(rangeX).Value
End If
            
       
       
       
       

       
       
     
If Target.Address(0, 0) = "AC4" Then
    
    Select Case Target
       Case "EDU1":  range1a = "B19"
        Case "EDU2":  range1a = "B20"
        Case "EDU3":  range1a = "B21"
        Case "EDU4":  range1a = "B22"
        Case "EXP1":  range1a = "B25"
        Case "EXP2":  range1a = "B26"
        Case "EXP3":  range1a = "B27"
        Case "EXP4":  range1a = "B28"
        Case "EXP5":  range1a = "B29"
        Case "EXP6":  range1a = "B30"
        Case "EXP7":  range1a = "B31"
        Case "EXP8":  range1a = "B32"
        Case "EXP9":  range1a = "B33"
        Case "EXP10": range1a = "B34"
        Case "EXP11": range1a = "B35"
        Case "EXP12": range1a = "B36"
        Case "EXP13": range1a = "B37"
        Case "EXP14": range1a = "B38"
        Case "EXP15": range1a = "B39"
        Case "EXP16": range1a = "B40"
        Case "EXP17": range1a = "B41"
        Case "EXP18": range1a = "B42"
        Case "EXP19": range1a = "B43"
        Case "EXP20": range1a = "B44"
        Case "A1": range1a = "B71"
        Case "A2": range1a = "B72"
        Case "PS1": range1a = "B83"
        Case "Comp1": range1a = "B95"
        Case "Comp2": range1a = "B96"
        Case "AEDU1": range1a = "B116"
        Case "AEDU2": range1a = "B117"
        Case "AEXP1": range1a = "B121"
        Case "AEXP2": range1a = "B122"
        Case "AEXP3": range1a = "B123"
     
     End Select
End If

If range1a <> "" Then
     Sheets("SBR").Range("AC3") = _
                Sheets("Process Info").Range(range1a).Value
End If
       
     

If Target.Address(0, 0) = "Z4" Then
    
    Select Case Target
              Case "EDU1":  range1a = "B19"
        Case "EDU2":  range1b = "B20"
        Case "EDU3":  range1b = "B21"
        Case "EDU4":  range1b = "B22"
        Case "EXP1":  range1b = "B25"
        Case "EXP2":  range1b = "B26"
        Case "EXP3":  range1b = "B27"
        Case "EXP4":  range1b = "B28"
        Case "EXP5":  range1b = "B29"
        Case "EXP6":  range1b = "B30"
        Case "EXP7":  range1b = "B31"
        Case "EXP8":  range1b = "B32"
        Case "EXP9":  range1b = "B33"
        Case "EXP10": range1b = "B34"
        Case "EXP11": range1b = "B35"
        Case "EXP12": range1b = "B36"
        Case "EXP13": range1b = "B37"
        Case "EXP14": range1b = "B38"
        Case "EXP15": range1b = "B39"
        Case "EXP16": range1b = "B40"
        Case "EXP17": range1b = "B41"
        Case "EXP18": range1b = "B42"
        Case "EXP19": range1b = "B43"
        Case "EXP20": range1b = "B44"
        Case "A1": range1b = "B71"
        Case "A2": range1b = "B72"
        Case "PS1": range1b = "B83"
        Case "Comp1": range1b = "B95"
        Case "Comp2": range1b = "B96"
        Case "AEDU1": range1b = "B116"
        Case "AEDU2": range1b = "B117"
        Case "AEXP1": range1b = "B121"
        Case "AEXP2": range1b = "B122"
        Case "AEXP3": range1b = "B123"
     
     End Select
End If

If range1b <> "" Then
     Sheets("SBR").Range("Z3") = _
                Sheets("Process Info").Range(range1b).Value
End If
       
    

If Target.Address(0, 0) = "AD4" Then
    
    Select Case Target
        Case "EDU1":  range1c = "B19"
        Case "EDU2":  range1c = "B20"
        Case "EDU3":  range1c = "B21"
        Case "EDU4":  range1c = "B22"
        Case "EXP1":  range1c = "B25"
        Case "EXP2":  range1c = "B26"
        Case "EXP3":  range1c = "B27"
        Case "EXP4":  range1c = "B28"
        Case "EXP5":  range1c = "B29"
        Case "EXP6":  range1c = "B30"
        Case "EXP7":  range1c = "B31"
        Case "EXP8":  range1c = "B32"
        Case "EXP9":  range1c = "B33"
        Case "EXP10": range1c = "B34"
        Case "EXP11": range1c = "B35"
        Case "EXP12": range1c = "B36"
        Case "EXP13": range1c = "B37"
        Case "EXP14": range1c = "B38"
        Case "EXP15": range1c = "B39"
        Case "EXP16": range1c = "B40"
        Case "EXP17": range1c = "B41"
        Case "EXP18": range1c = "B42"
        Case "EXP19": range1c = "B43"
        Case "EXP20": range1c = "B44"
        Case "A1": range1c = "B71"
        Case "A2": range1c = "B72"
        Case "PS1": range1c = "B83"
        Case "Comp1": range1c = "B95"
        Case "Comp2": range1c = "B96"
        Case "AEDU1": range1c = "B116"
        Case "AEDU2": range1c = "B117"
        Case "AEXP1": range1c = "B121"
        Case "AEXP2": range1c = "B122"
        Case "AEXP3": range1c = "B123"
     
     End Select
End If

If range1c <> "" Then
     Sheets("SBR").Range("AD3") = _
                Sheets("Process Info").Range(range1c).Value
End If
       
     
    
     
     
     
     
If Target.Address(0, 0) = "AE4" Then
    
    Select Case Target
        Case "EDU1":  range1x = "B19"
        Case "EDU2":  range1x = "B20"
        Case "EDU3":  range1x = "B21"
        Case "EDU4":  range1x = "B22"
        Case "EXP1":  range1x = "B25"
        Case "EXP2":  range1x = "B26"
        Case "EXP3":  range1x = "B27"
        Case "EXP4":  range1x = "B28"
        Case "EXP5":  range1x = "B29"
        Case "EXP6":  range1x = "B30"
        Case "EXP7":  range1x = "B31"
        Case "EXP8":  range1x = "B32"
        Case "EXP9":  range1x = "B33"
        Case "EXP10": range1x = "B34"
        Case "EXP11": range1x = "B35"
        Case "EXP12": range1x = "B36"
        Case "EXP13": range1x = "B37"
        Case "EXP14": range1x = "B38"
        Case "EXP15": range1x = "B39"
        Case "EXP16": range1x = "B40"
        Case "EXP17": range1x = "B41"
        Case "EXP18": range1x = "B42"
        Case "EXP19": range1x = "B43"
        Case "EXP20": range1x = "B44"
        Case "A1": range1x = "B71"
        Case "A2": range1x = "B72"
        Case "PS1": range1x = "B83"
        Case "Comp1": range1x = "B95"
        Case "Comp2": range1x = "B96"
        Case "AEDU1": range1x = "B116"
        Case "AEDU2": range1x = "B117"
        Case "AEXP1": range1x = "B121"
        Case "AEXP2": range1x = "B122"
        Case "AEXP3": range1x = "B123"
     
     End Select
End If

If range1x <> "" Then
     Sheets("SBR").Range("AE3") = _
                Sheets("Process Info").Range(range1x).Value
End If
       
     

       
     


If Target.Address(0, 0) = "AF4" Then
    
    Select Case Target
        Case "EDU1":  range1s = "B19"
        Case "EDU2":  range1s = "B20"
        Case "EDU3":  range1s = "B21"
        Case "EDU4":  range1s = "B22"
        Case "EXP1":  range1s = "B25"
        Case "EXP2":  range1s = "B26"
        Case "EXP3":  range1s = "B27"
        Case "EXP4":  range1s = "B28"
        Case "EXP5":  range1s = "B29"
        Case "EXP6":  range1s = "B30"
        Case "EXP7":  range1s = "B31"
        Case "EXP8":  range1s = "B32"
        Case "EXP9":  range1s = "B33"
        Case "EXP10": range1s = "B34"
        Case "EXP11": range1s = "B35"
        Case "EXP12": range1s = "B36"
        Case "EXP13": range1s = "B37"
        Case "EXP14": range1s = "B38"
        Case "EXP15": range1s = "B39"
        Case "EXP16": range1s = "B40"
        Case "EXP17": range1s = "B41"
        Case "EXP18": range1s = "B42"
        Case "EXP19": ranges1s = "B43"
        Case "EXP20": range1s = "B44"
        Case "A1": range1s = "B71"
        Case "A2": range1s = "B72"
        Case "PS1": range1s = "B83"
        Case "Comp1": range1s = "B95"
        Case "Comp2": range1s = "B96"
        Case "AEDU1": range1s = "B116"
        Case "AEDU2": range1s = "B117"
        Case "AEXP1": range1s = "B121"
        Case "AEXP2": range1s = "B122"
        Case "AEXP3": range1s = "B123"
     
     End Select
End If

If range1s <> "" Then
     Sheets("SBR").Range("AF3") = _
                Sheets("Process Info").Range(range1s).Value
End If
       
     

If Target.Address(0, 0) = "AG4" Then
    
    Select Case Target
         Case "EDU1":  range1q = "B19"
        Case "EDU2":  range1q = "B20"
        Case "EDU3":  range1q = "B21"
        Case "EDU4":  range1q = "B22"
        Case "EXP1":  range1q = "B25"
        Case "EXP2":  range1q = "B26"
        Case "EXP3":  range1q = "B27"
        Case "EXP4":  range1q = "B28"
        Case "EXP5":  range1q = "B29"
        Case "EXP6":  range1q = "B30"
        Case "EXP7":  range1q = "B31"
        Case "EXP8":  range1q = "B32"
        Case "EXP9":  range1q = "B33"
        Case "EXP10": range1q = "B34"
        Case "EXP11": range1q = "B35"
        Case "EXP12": range1q = "B36"
        Case "EXP13": range1q = "B37"
        Case "EXP14": range1q = "B38"
        Case "EXP15": range1q = "B39"
        Case "EXP16": range1q = "B40"
        Case "EXP17": range1q = "B41"
        Case "EXP18": range1q = "B42"
        Case "EXP19": range1q = "B43"
        Case "EXP20": range1q = "B44"
        Case "A1": range1q = "B71"
        Case "A2": range1q = "B72"
        Case "PS1": range1q = "B83"
        Case "Comp1": range1q = "B95"
        Case "Comp2": range1q = "B96"
        Case "AEDU1": range1q = "B116"
        Case "AEDU2": range1q = "B117"
        Case "AEXP1": range1q = "B121"
        Case "AEXP2": range1q = "B122"
        Case "AEXP3": range1q = "B123"
     End Select
End If

If range1q <> "" Then
     Sheets("SBR").Range("AG3") = _
                Sheets("Process Info").Range(range1q).Value
End If
       
     

If Target.Address(0, 0) = "AH4" Then
    
    Select Case Target
       
       
       
       
        Case "EDU1":  range1e = "B19"
        Case "EDU2":  range1e = "B20"
        Case "EDU3":  range1e = "B21"
        Case "EDU4":  range1e = "B22"
        Case "EXP1":  range1e = "B25"
        Case "EXP2":  range1e = "B26"
        Case "EXP3":  range1e = "B27"
        Case "EXP4":  range1e = "B28"
        Case "EXP5":  range1e = "B29"
        Case "EXP6":  range1e = "B30"
        Case "EXP7":  range1e = "B31"
        Case "EXP8":  range1e = "B32"
        Case "EXP9":  range1e = "B33"
        Case "EXP10": range1e = "B34"
        Case "EXP11": range1e = "B35"
        Case "EXP12": range1e = "B36"
        Case "EXP13": range1e = "B37"
        Case "EXP14": range1e = "B38"
        Case "EXP15": range1e = "B39"
        Case "EXP16": range1e = "B40"
        Case "EXP17": range1e = "B41"
        Case "EXP18": range1e = "B42"
        Case "EXP19": range1e = "B43"
        Case "EXP20": range1e = "B44"
        Case "A1":    range1e = "B71"
        Case "A2":    range1e = "B72"
        Case "PS1":   range1e = "B83"
        Case "Comp1": range1e = "B95"
        Case "Comp2": range1e = "B96"
        Case "AEDU1": range1e = "B116"
        Case "AEDU2": range1e = "B117"
        Case "AEXP1": range1e = "B121"
        Case "AEXP2": range1e = "B122"
        Case "AEXP3": range1e = "B123"
       
    
     End Select
End If

If range1e <> "" Then
     Sheets("SBR").Range("AH3") = _
                Sheets("Process Info").Range(range1e).Value
End If
        
        
If Target.Address(0, 0) = "AI4" Then
    
    Select Case Target
      
       Case "EDU1":  range1n = "B19"
        Case "EDU2":  range1n = "B20"
        Case "EDU3":  range1n = "B21"
        Case "EDU4":  range1n = "B22"
        Case "EXP1":  range1n = "B25"
        Case "EXP2":  range1n = "B26"
        Case "EXP3":  range1n = "B27"
        Case "EXP4":  range1n = "B28"
        Case "EXP5":  range1n = "B29"
        Case "EXP6":  range1n = "B30"
        Case "EXP7":  range1n = "B31"
        Case "EXP8":  range1n = "B32"
        Case "EXP9":  range1n = "B33"
        Case "EXP10": range1n = "B34"
        Case "EXP11": range1n = "B35"
        Case "EXP12": range1n = "B36"
        Case "EXP13": range1n = "B37"
        Case "EXP14": range1n = "B38"
        Case "EXP15": range1n = "B39"
        Case "EXP16": range1n = "B40"
        Case "EXP17": range1n = "B41"
        Case "EXP18": range1n = "B42"
        Case "EXP19": range1n = "B43"
        Case "EXP20": range1n = "B44"
        Case "A1": range1n = "B71"
        Case "A2": range1n = "B72"
        Case "PS1": range1n = "B83"
        Case "Comp1": range1n = "B95"
        Case "Comp2": range1n = "B96"
        Case "AEDU1": range1n = "B116"
        Case "AEDU2": range1n = "B117"
        Case "AEXP1": range1n = "B121"
        Case "AEXP2": range1n = "B122"
        Case "AEXP3": range1n = "B123"
      
     End Select
End If

If range1n <> "" Then
     Sheets("SBR").Range("AI3") = _
                Sheets("Process Info").Range(range1n).Value
End If
       
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
end sub
Click to expand...

I'm a huge noob when it comes to VBA and I get VERY EXCITED when I know the answer to something as I recently experienced this issue.

All I did was create a CALL function split the code in half or in thirds and add as many CALL functions as needed.

Sub EXAMPLE()

DATA
DATA
DATA ETC

CALL EXAMPLE2()

END SUB

SUB EXAMPLE2()

DATACONTINUED
DATACONTINUED
DATACONTINUED ETC

END SUB

rinse and repeat when needed.

Should be good as gold.

Luke
 
Upvote 0
You appear to be repeating a lot of code there - the Select Case statement appears to involve the exact same values each time.

Which range is the code meant to work for?
 
Upvote 0
Hello ,

here is my ranges line 4 [AK... BN] and my data validation are situated is the same row too line 3 [AK..BN] also i use the same values
thanks for ur help :)
 
Upvote 0
You could shorten this further with some arrays, but it looks like you want something like this:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell                 As Range
    Dim range1                As String

    If Not Intersect(Target, Range("AK4:BN4")) Is Nothing Then
        For Each rCell In Intersect(Target, Range("AK4:BN4")).Cells

            Select Case rCell.value
                Case "EDU1": range1 = "B19"
                Case "EDU2": range1 = "B20"
                Case "EDU3": range1 = "B21"
                Case "EDU4": range1 = "B22"
                Case "EXP1": range1 = "B25"
                Case "EXP2": range1 = "B26"
                Case "EXP3": range1 = "B27"
                Case "EXP4": range1 = "B28"
                Case "EXP5": range1 = "B29"
                Case "EXP6": range1 = "B30"
                Case "EXP7": range1 = "B31"
                Case "EXP8": range1 = "B32"
                Case "EXP9": range1 = "B33"
                Case "EXP10": range1 = "B34"
                Case "EXP11": range1 = "B35"
                Case "EXP12": range1 = "B36"
                Case "EXP13": range1 = "B37"
                Case "EXP14": range1 = "B38"
                Case "EXP15": range1 = "B39"
                Case "EXP16": range1 = "B40"
                Case "EXP17": range1 = "B41"
                Case "EXP18": range1 = "B42"
                Case "EXP19": range1 = "B43"
                Case "EXP20": range1 = "B44"
                Case "A1": range1 = "B71"
                Case "A2": range1 = "B72"
                Case "PS1": range1 = "B83"
                Case "Comp1": range1 = "B95"
                Case "Comp2": range1 = "B96"
                Case "AEDU1": range1 = "B116"
                Case "AEDU2": range1 = "B117"
                Case "AEXP1": range1 = "B121"
                Case "AEXP2": range1 = "B122"
                Case "AEXP3": range1 = "B123"
            End Select
            If range1 <> "" Then
                rCell.Offset(-1).value = Sheets("Process Info").Range(range1).value
            End If
        Next rCell
    End If

End Sub
 
Upvote 0
Thank you very much for your help
it works good if i would like to work other ranges and different values can use the code :


Code: 
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    Dim rCell                 As Range
    Dim range1                As String

    If Not Intersect(Target, Range("AK4:BN4")) Is Nothing Then
        For Each rCell In Intersect(Target, Range("AK4:BN4")).Cells

            Select Case rCell.value
                Case "EDU1": range1 = "B19"
                Case "EDU2": range1 = "B20"
                Case "EDU3": range1 = "B21"
                Case "EDU4": range1 = "B22"
                Case "EXP1": range1 = "B25"
                Case "EXP2": range1 = "B26"
                Case "EXP3": range1 = "B27"
                Case "EXP4": range1 = "B28"
                Case "EXP5": range1 = "B29"
                Case "EXP6": range1 = "B30"
                Case "EXP7": range1 = "B31"
                Case "EXP8": range1 = "B32"
                Case "EXP9": range1 = "B33"
                Case "EXP10": range1 = "B34"
                Case "EXP11": range1 = "B35"
                Case "EXP12": range1 = "B36"
                Case "EXP13": range1 = "B37"
                Case "EXP14": range1 = "B38"
                Case "EXP15": range1 = "B39"
                Case "EXP16": range1 = "B40"
                Case "EXP17": range1 = "B41"
                Case "EXP18": range1 = "B42"
                Case "EXP19": range1 = "B43"
                Case "EXP20": range1 = "B44"
                Case "A1": range1 = "B71"
                Case "A2": range1 = "B72"
                Case "PS1": range1 = "B83"
                Case "Comp1": range1 = "B95"
                Case "Comp2": range1 = "B96"
                Case "AEDU1": range1 = "B116"
                Case "AEDU2": range1 = "B117"
                Case "AEXP1": range1 = "B121"
                Case "AEXP2": range1 = "B122"
                Case "AEXP3": range1 = "B123"
            End Select
            If range1 <> "" Then
                rCell.Offset(-1).value = Sheets("Process Info").Range(range1).value
            End If
        Next rCell
    


If Not Intersect(Target, Range("CM4:DZ4")) Is Nothing Then
        For Each rCell In Intersect(Target, Range("AK4:BN4")).Cells

            Select Case rCell.value
                Case "K1": range2 = "B65"
                Case "k2": range2 = "B66"
                Case "k3": range2 = "B67"
                Case "k4": range2 = "B68"
                Case "k5": range2 = "B69"
                
            End Select
            If range1 <> "" Then
                rCell.Offset(-1).value = Sheets("Process Info").Range(range1).value
            End If
        Next rCell
    End If [COLOR=#333333]End Sub[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel Not Responding
Replies
0
Views
705
JeremySun
J

Forum statistics

Threads
1,215,528
Messages
6,125,342
Members
449,218
Latest member
Excel Master

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