Como puedo hacer una Marcos que calcule una nota ligada a unos valores de celda?
Esto va ligado a la Macros anterior, luego de ingresar todas las notas pedidas por msg quiero calcular una nota final con distintas ponderaciones que dependen del cargo del trabajador, como puedo hacer eso ¿?
Respuesta de Adriel Ortiz Mangia
1
H o l a
¿Envíame a mi Email [email protected], con ejemplos que debemos de calcular?, cual columna porque son varias.
H o l a
te paso la macro
Private Sub CommandButton1_Click()
'Por Aortiz
Set h1 = Sheets("hoja1")
Set r = h1.Columns("A")
Set b = r.Find(TextBox1, lookat:=xlWhole)
If Not b Is Nothing Then
If h1.Cells(b.Row, "C") = "secretaria" Then
h1.Cells(b.Row, "A") = Val(TextBox1)
h1.Cells(b.Row, "B") = TextBox2
h1.Cells(b.Row, "C") = TextBox3
h1.Cells(b.Row, "D") = TextBox4
h1.Cells(b.Row, "E") = Val(TextBox5)
h1.Cells(b.Row, "F") = Val(TextBox6)
h1.Cells(b.Row, "G") = Val(TextBox7)
h1.Cells(b.Row, "H") = Val(TextBox8)
h1.Cells(b.Row, "I") = Val(TextBox9)
h1.Cells(b.Row, "J") = Val(TextBox10)
h1.Cells(b.Row, "K") = Val(TextBox11)
h1.Cells(b.Row, "L") = Val(TextBox12)
h1.Cells(b.Row, "M") = Val(TextBox13)
h1.Cells(b.Row, "N") = Val(TextBox14)
h1.Cells(b.Row, "O") = Val(TextBox15)
h1.Cells(b.Row, "P") = Val(TextBox16)
h1.Cells(b.Row, "Q") = Val(TextBox17)
h1.Cells(b.Row, "R") = Val(TextBox18)
h1.Cells(b.Row, "S") = Val(TextBox19)
h1.Cells(b.Row, "T") = TextBox20
h1.Cells(b.Row, "U") = TextBox21
h1.Cells(b.Row, "V") = TextBox22
h1.Cells(b.Row, "W") = TextBox23
'
MET = 0.5
AG = 0.3
FALL = 0.2
'
Sum = (Val(TextBox6) * 0.125 + Val(TextBox7) * 0.125 + Val(TextBox8) * 0.1 + Val(TextBox9) * 0.15 + _
Val(TextBox10) * 0.2 + Val(TextBox11) * 0.2 + Val(TextBox12) * 0.2) * (MET + 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 + FALL)
'
h1.Cells(b.Row, "X") = Format(Sum, "##0.00")
'
Call LIMPIAR
'
MsgBox "Datos actualizados"
ElseIf h1.Cells(b.Row, "C") = "recepcionista" Then
h1.Cells(b.Row, "A") = Val(TextBox1)
h1.Cells(b.Row, "B") = TextBox2
h1.Cells(b.Row, "C") = TextBox3
h1.Cells(b.Row, "D") = TextBox4
h1.Cells(b.Row, "E") = Val(TextBox5)
h1.Cells(b.Row, "F") = Val(TextBox6)
h1.Cells(b.Row, "G") = Val(TextBox7)
h1.Cells(b.Row, "H") = Val(TextBox8)
h1.Cells(b.Row, "I") = Val(TextBox9)
h1.Cells(b.Row, "J") = Val(TextBox10)
h1.Cells(b.Row, "K") = Val(TextBox11)
h1.Cells(b.Row, "L") = Val(TextBox12)
h1.Cells(b.Row, "M") = Val(TextBox13)
h1.Cells(b.Row, "N") = Val(TextBox14)
h1.Cells(b.Row, "O") = Val(TextBox15)
h1.Cells(b.Row, "P") = Val(TextBox16)
h1.Cells(b.Row, "Q") = Val(TextBox17)
h1.Cells(b.Row, "R") = Val(TextBox18)
h1.Cells(b.Row, "S") = Val(TextBox19)
h1.Cells(b.Row, "T") = TextBox20
h1.Cells(b.Row, "U") = TextBox21
h1.Cells(b.Row, "V") = TextBox22
h1.Cells(b.Row, "W") = TextBox23
'
MET = 0.5
AG = 0.3
FALL = 0.2
'
Sum = (Val(TextBox6) * 0.125 + Val(TextBox7) * 0.125 + Val(TextBox8) * 0.1 + Val(TextBox9) * 0.15 + _
Val(TextBox10) * 0.2 + Val(TextBox11) * 0.2 + Val(TextBox12) * 0.2) * (MET + 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 + FALL)
'
h1.Cells(b.Row, "X") = Format(Sum, "##0.00")
'
Call LIMPIAR
'
MsgBox "Datos actualizados"
ElseIf h1.Cells(b.Row, "C") = "maestro de cocina" Then
h1.Cells(b.Row, "A") = Val(TextBox1)
h1.Cells(b.Row, "B") = TextBox2
h1.Cells(b.Row, "C") = TextBox3
h1.Cells(b.Row, "D") = TextBox4
h1.Cells(b.Row, "E") = Val(TextBox5)
h1.Cells(b.Row, "F") = Val(TextBox6)
h1.Cells(b.Row, "G") = Val(TextBox7)
h1.Cells(b.Row, "H") = Val(TextBox8)
h1.Cells(b.Row, "I") = Val(TextBox9)
h1.Cells(b.Row, "J") = Val(TextBox10)
h1.Cells(b.Row, "K") = Val(TextBox11)
h1.Cells(b.Row, "L") = Val(TextBox12)
h1.Cells(b.Row, "M") = Val(TextBox13)
h1.Cells(b.Row, "N") = Val(TextBox14)
h1.Cells(b.Row, "O") = Val(TextBox15)
h1.Cells(b.Row, "P") = Val(TextBox16)
h1.Cells(b.Row, "Q") = Val(TextBox17)
h1.Cells(b.Row, "R") = Val(TextBox18)
h1.Cells(b.Row, "S") = Val(TextBox19)
h1.Cells(b.Row, "T") = TextBox20
h1.Cells(b.Row, "U") = TextBox21
h1.Cells(b.Row, "V") = TextBox22
h1.Cells(b.Row, "W") = TextBox23
'
MET = 0.5
AG = 0.3
FALL = 0.2
'
Sum = (Val(TextBox6) * 0.125 + Val(TextBox7) * 0.125 + Val(TextBox8) * 0.1 + Val(TextBox9) * 0.15 + _
Val(TextBox10) * 0.2 + Val(TextBox11) * 0.2 + Val(TextBox12) * 0.2) * (MET + 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 + FALL)
'
h1.Cells(b.Row, "X") = Format(Sum, "##0.00")
'
Call LIMPIAR
'
MsgBox "Datos actualizados"
End If
End If
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)
'Por Aortiz
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 dato 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 SubValora para finalizar saludos!
