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 ¿?
1 Respuesta
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!
