Error de compilación:procedimiento demasiado largo
Necesito realizar una Marcos en la cual busca al trabajador de acuerdo su rut, luego pide ingresar una nota, y posteriormente las guarda y calcula una nota final, que su cálculo depende del cargo del trabajador, ya que cada cargo tiene distintas fórmulas y ponderaciones de las notas. Como son aproximadamente 120 cargos son muchos códigos y me tira un error de compilación de que el procedimiento es demasiado largo.
1 respuesta
Respuesta de Dante Amor
1
H o la:
Te anexo el código simplificado:
Private Sub CommandButton1_Click()
'Por.Dante Amor
Set h1 = Sheets("Hoja1")
Set h2 = Sheets("Pondera")
If IsNumeric(TextBox1) Then rut = Val(TextBox1) Else rut = TextBox1
Set b = h1.Columns("A").Find(rut, lookat:=xlWhole)
If Not b Is Nothing Then
fila = b.Row
cargo = h1.Cells(fila, "C")
Set c = h2.Columns("A").Find(cargo, lookat:=xlWhole)
If Not c Is Nothing Then
sum1 = (Val(TextBox6) * h2.Cells(c.Row, "B") + _
Val(TextBox7) * h2.Cells(c.Row, "C") + _
Val(TextBox8) * h2.Cells(c.Row, "D") + _
Val(TextBox9) * h2.Cells(c.Row, "E") + _
Val(TextBox10) * h2.Cells(c.Row, "F") + _
Val(TextBox11) * h2.Cells(c.Row, "G") + _
Val(TextBox12) * h2.Cells(c.Row, "H")) * h2.Cells(c.Row, "I")
sum2 = (Val(TextBox13) * h2.Cells(c.Row, "J") + _
Val(TextBox14) * h2.Cells(c.Row, "K") + _
Val(TextBox15) * h2.Cells(c.Row, "L") + _
Val(TextBox16) * h2.Cells(c.Row, "M") + _
Val(TextBox17) * h2.Cells(c.Row, "N")) * h2.Cells(c.Row, "O")
sum3 = (Val(TextBox18) * h2.Cells(c.Row, "P") + _
Val(TextBox19) * h2.Cells(c.Row, "Q")) * h2.Cells(c.Row, "R")
resul = sum1 + sum2 + sum3
h1.Cells(fila, "X") = Format(resul, "##0.00")
Call Cargar(h1, fila)
Call Limpiar
MsgBox "Datos actualizados"
Else
MsgBox "El cargo no existe en la hoja de ponderaciones"
End If
Else
MsgBox "El Rut no existe"
End If
End Sub
'Ejemplo
'Sum = (Val(TextBox6) * (50 / (3 * 100)) + Val(TextBox7) * (50 / (3 * 100)) + Val(TextBox8) * (50 / (3 * 100)) + Val(TextBox9) * 0 + _
Val(TextBox10) * 0.3 + Val(TextBox11) * 0.2 + Val(TextBox12) * 0) * META + (Val(TextBox13) * 0.2 + Val(TextBox14) * 0.2 + _
Val(TextBox15) * 0.2 + Val(TextBox16) * 0.2 + Val(TextBox17) * 0.2) * AG + (Val(TextBox18) * 0.1 + Val(TextBox19) * 0.1) * FALLA
'
Sub Cargar(h1, fila)
h1.Cells(fila, "A") = Val(TextBox1)
h1.Cells(fila, "B") = TextBox2
h1.Cells(fila, "C") = TextBox3
h1.Cells(fila, "D") = TextBox4
h1.Cells(fila, "E") = TextBox5
h1.Cells(fila, "F") = Val(TextBox6)
h1.Cells(fila, "G") = Val(TextBox7)
h1.Cells(fila, "H") = Val(TextBox8)
h1.Cells(fila, "I") = Val(TextBox9)
h1.Cells(fila, "J") = Val(TextBox10)
h1.Cells(fila, "K") = Val(TextBox11)
h1.Cells(fila, "L") = Val(TextBox12)
h1.Cells(fila, "M") = Val(TextBox13)
h1.Cells(fila, "N") = Val(TextBox14)
h1.Cells(fila, "O") = Val(TextBox15)
h1.Cells(fila, "P") = Val(TextBox16)
h1.Cells(fila, "Q") = Val(TextBox17)
h1.Cells(fila, "R") = Val(TextBox18)
h1.Cells(fila, "S") = Val(TextBox19)
h1.Cells(fila, "T") = TextBox20
h1.Cells(fila, "U") = TextBox21
h1.Cells(fila, "V") = TextBox22
h1.Cells(fila, "W") = TextBox23
End Sub
'
Private Sub TextBox1_Change()
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub
'
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1 = "" Then Exit Sub
Set h = Sheets("hoja1") 'nombre de la hoja
Set b = h.Columns("A").Find(TextBox1, lookat:=xlWhole) 'Busca en la columna A
If Not b Is Nothing Then 'Si lo encuentra
TextBox1 = h.Cells(b.Row, "A").Value
TextBox2 = h.Cells(b.Row, "B").Value
TextBox3 = h.Cells(b.Row, "C").Value
TextBox4 = h.Cells(b.Row, "D").Value
TextBox5 = h.Cells(b.Row, "E").Value
TextBox6 = h.Cells(b.Row, "F").Value
TextBox7 = h.Cells(b.Row, "G").Value
TextBox8 = h.Cells(b.Row, "H").Value
TextBox9 = h.Cells(b.Row, "I").Value
TextBox10 = h.Cells(b.Row, "J").Value
TextBox11 = h.Cells(b.Row, "K").Value
TextBox12 = h.Cells(b.Row, "L").Value
TextBox13 = h.Cells(b.Row, "M").Value
TextBox14 = h.Cells(b.Row, "N").Value
TextBox15 = h.Cells(b.Row, "O").Value
TextBox16 = h.Cells(b.Row, "P").Value
TextBox17 = h.Cells(b.Row, "Q").Value
TextBox18 = h.Cells(b.Row, "R").Value
TextBox19 = h.Cells(b.Row, "S").Value
TextBox20 = h.Cells(b.Row, "T").Value
TextBox21 = h.Cells(b.Row, "U").Value
TextBox22 = h.Cells(b.Row, "V").Value
TextBox23 = h.Cells(b.Row, "W").Value
TextBox24 = h.Cells(b.Row, "X").Value
Else
MsgBox "El RUT no existe" 'si no existe
End If
End Sub
'
Private Sub UserForm_Activate()
TextBox24.Enabled = False
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub
'
Sub Limpiar()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox24 = ""
End Sub'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
