Vba Aplicación
'
' Macro1 Macro
' new_formula
'
Dim y As Integer
Dim j As Integer
'Application.ScreenUpdating = False 'Evita el parpadeo y que se vea el proceso al correr la macro
k = Worksheets.Count
y = InputBox("Que columna empieza? no dejar 0", , 0)
For i = 1 To k
If Worksheets(k - i + 1).Visible Then
Worksheets(k - i + 1).Select
For j = 38 To 398Step 12
Cells(j, y).Select
'ActiveCell.FormulaR1C1 = "=R[8]C"
'Cells(38, y).Select
ActiveCell.FormulaR1C1 = _
"=IF(SUM(RC[-5]:RC[-1])>=SUM(R[-2]C[-5]:R[-2]C[-1]),0,(SUM(R[-2]C[-5]:R[-2]C[-1])-SUM(RC[-5]:RC[-1])))"
' es lo mismo
'=IF(SUM(W38:AA38)>=SUM(W36:AB36),0,(SUM(W36:AB36)-SUM(W38:AA38)))Next
End If
Next i
Application.ScreenUpdating = True 'Evita el parpadeo y que se vea el proceso al correr la macro
End Sub
Global dep(1 To 103) As String
Sub jalar_datos_allocation()
x = ActiveWorkbook.Name
For i = 1 To 40
Cells(1, 44 + 2 * i) = x
Next i
'
libro = ActiveWorkbook.Name
s = 0
For i = 1 To 500
IfNot IsEmpty(Cells(10 + i, 1)) Then
s = s + 1
Else
i = 500
End If
Next i
'borra datos anteriores
Range("I11:L11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("P11:S11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("V11:X11").SelectRange(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("Y11").Select
Range(Selection, Selection.End(xlDown)).Select
Range("AB11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("M11:N11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("Y11").SelectRange(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A11").Select
'nos da la jerarquia
dept = Cells(2, 1).Value
subdept = Cells(2, 2).Value
clase = Cells(2, 3).Value
subclase = Cells(2, 4).Value
dept2 = Cells(4, 1).Value
subdept2 = Cells(4, 2).Value
clase2 = Cells(4, 3).Value
subclase2 = Cells(4,4).Value
'informacion JERARQUIA
If Cells(2, 1) = "" Then
MsgBox ("NO HAS LLENADO EL DEPARTAMENTO")
Range("A2").Select
Exit Sub
'llamamos al userform
' UserForm1.Show
'INFORMACION DEL MES que se va a analizar
End If
If Cells(6, 2) = "" Then
C = InputBox("SELECCIONA EL MES EN QUE SE VA A ANALIZAR " & Chr(13) & Chr(13) & _
"1: Enero"& Chr(13) & "2: Febrero" & Chr(13) & "3: Marzo" & Chr(13) & _
"4: Abril" & Chr(13) & "5: Mayo" & Chr(13) & "6: Junio" & Chr(13) & _
"7: Julio" & Chr(13) & "8: Agosto" & Chr(13) & "9: Septiembre" & Chr(13) & _
"10: Octubre" & Chr(13) & "11: Noviembre" & Chr(13) & "12: Diciembre" & Chr(13), "MES DE ANALISIS")
If C = 1 Then
Cells(6, 2) = "ENERO"
End IfIf C = 2 Then
Cells(6, 2) = "FEBRERO"
End If
If C = 3 Then
Cells(6, 2) = "MARZO"
End If
If C = 4 Then
Cells(6, 2) = "ABRIL"
End If
If C = 5 Then
Cells(6, 2) = "MAYO"
End If
If C = 6 Then
Cells(6, 2) = "JUNIO"
End If
If C = 7 Then
Cells(6, 2) = "JULIO"End If
If C = 8 Then
Cells(6, 2) = "AGOSTO"
End If
If C = 9 Then
Cells(6, 2) = "SEPTIEMBRE"
End If
If C = 10 Then
Cells(6, 2) = "OCTUBRE"
End If
If C = 11 Then
Cells(6, 2) = "NOVIEMBRE"
End If
If C = 12 Then
Cells(6, 2) = "DICIEMBRE"
End If
If...
Regístrate para leer el documento completo.