| Respuesta: Ayuda con macro
Código:
formula1 = "='[Casos_Cerrados_Dic_2009.xls]MAR-ABR'!" & grilla1
formula2 = "='[Casos_Cerrados_Dic_2009.xls]MAR-ABR'!" & grilla2
formula3 = "='[Casos_Cerrados_Dic_2009.xls]MAR-ABR'!" & grilla3
formula4 = "='[Casos_Cerrados_Dic_2009.xls]MAR-ABR'!" & grilla4
formula5 = "='[Casos_Cerrados_Dic_2009.xls]MAR-ABR'!" & grilla5
End If
If opc = 3 Then
letra = Switch(i = 3, "B", i = 4, "C", i = 5, "D", i = 6, "E", i = 7, "F", i = 8, "G", _
i = 9, "H", i = 10, "I", i = 11, "J", i = 12, "K", i = 13, "L", i = 14, "M", i = 15, "N", _
i = 16, "O", i = 17, "P", i = 18, "Q", i = 19, "R", i = 20, "S", i = 21, "T", i = 22, "U", i = 23, _
"V", i = 24, "W", i = 25, "X", i = 26, "Y", i = 27, "Z", i = 28, "AA", i = 29, "AB", i = 30, "AC", _
i = 31, "AD", i = 32, "AE", i = 33, "AF", i = 34, "AG", i = 35, "AH", i = 36, "AI", i = 37, "AJ", _
i = 38, "AK", i = 39, "AL", i = 40, "AM", i = 41, "AN", i = 42, "AO", i = 43, "AP", i = 44, "AQ", _
i = 45, "AR", i = 46, "AS", i = 47, "AT", i = 48, "AU", i = 49, "AV", i = 50, "AW", i = 51, "AX", _
i = 52, "AY", i = 53, "AZ", i = 54, "BA", i = 55, "BB", i = 56, "BC", i = 57, "BD", i = 58, "BE", _
i = 59, "BF", i = 60, "BG", i = 61, "BH", i = 62, "BI", i = 63, "BJ", i = 64, "BK", _
i = 65, "BL", i = 66, "BM", i = 67, "BN", i = 68, "BO", i = 69, "BP", i = 70, "BQ", _
i = 71, "BR", i = 72, "BS", i = 73, "BT", i = 74, "BU", i = 75, "BV", i = 76, "BX", _
i = 77, "BY", i = 78, "BZ", i = 79, "CA", i = 80, "CB", i = 81, "CC", i = 82, "CD", _
i = 83, "CE", i = 84, "CF", i = 85, "CG", i = 86, "CH", i = 87, "CI", i = 88, "CJ", _
i = 89, "CK", i = 90, "CL", i = 91, "CM", i = 92, "CN", i = 93, "CO", i = 94, "CP", _
i = 95, "CQ", i = 96, "CR", i = 97, "CS", i = 98, "CT", i = 99, "CU", i = 100, "CV", _
i = 101, "CW", i = 102, "CX", i = 103, "CY", i = 104, "CZ", i = 105, "DA", i = 106, "DB", _
i = 107, "DC", i = 108, "DD", i = 109, "DE", i = 110, "DF", i = 111, "DG", i = 112, "DH", _
i = 113, "DI", i = 114, "DJ", i = 115, "DK", i = 116, "DL", i = 117, "DM", i = 118, "DN", _
i = 119, "DO", i = 120, "DP", i = 121, "DQ", i = 122, "DR", i = 123, "DS", _
i = 124, "DT", i = 125, "DU", i = 126, "DV", i = 127, "DW", i = 128, "DX", _
i = 129, "DY", i = 130, "DZ", i = 131, "EA", i = 132, "EB", i = 133, "EC", _
i = 134, "ED", i = 135, "EE", i = 136, "EF", i = 137, "EG", i = 138, "EH")
grilla1 = "$" + letra + "$3"
grilla2 = "$" + letra + "$4"
grilla3 = "$" + letra + "$5"
grilla4 = "$" + letra + "$6"
grilla5 = "$" + letra + "$7"
formula1 = "='[Casos_Cerrados_Dic_2009.xls]MAY-JUN'!" & grilla1
formula2 = "='[Casos_Cerrados_Dic_2009.xls]MAY-JUN'!" & grilla2
formula3 = "='[Casos_Cerrados_Dic_2009.xls]MAY-JUN'!" & grilla3
formula4 = "='[Casos_Cerrados_Dic_2009.xls]MAY-JUN'!" & grilla4
formula5 = "='[Casos_Cerrados_Dic_2009.xls]MAY-JUN'!" & grilla5
End If
If opc = 4 Then
letra = Switch(i = 3, "B", i = 4, "C", i = 5, "D", i = 6, "E", i = 7, "F", i = 8, "G", _
i = 9, "H", i = 10, "I", i = 11, "J", i = 12, "K", i = 13, "L", i = 14, "M", i = 15, "N", _
i = 16, "O", i = 17, "P", i = 18, "Q", i = 19, "R", i = 20, "S", i = 21, "T", i = 22, "U", i = 23, _
"V", i = 24, "W", i = 25, "X", i = 26, "Y", i = 27, "Z", i = 28, "AA", i = 29, "AB", i = 30, "AC", _
i = 31, "AD", i = 32, "AE", i = 33, "AF", i = 34, "AG", i = 35, "AH", i = 36, "AI", i = 37, "AJ", _
i = 38, "AK", i = 39, "AL", i = 40, "AM", i = 41, "AN", i = 42, "AO", i = 43, "AP", i = 44, "AQ", _
i = 45, "AR", i = 46, "AS", i = 47, "AT", i = 48, "AU", i = 49, "AV", i = 50, "AW", i = 51, "AX", _
i = 52, "AY", i = 53, "AZ", i = 54, "BA", i = 55, "BB", i = 56, "BC", i = 57, "BD", i = 58, "BE", _
i = 59, "BF", i = 60, "BG", i = 61, "BH", i = 62, "BI", i = 63, "BJ", i = 64, "BK", _
i = 65, "BL", i = 66, "BM", i = 67, "BN", i = 68, "BO", i = 69, "BP", i = 70, "BQ", _
i = 71, "BR", i = 72, "BS", i = 73, "BT", i = 74, "BU", i = 75, "BV", i = 76, "BX", _
i = 77, "BY", i = 78, "BZ", i = 79, "CA", i = 80, "CB", i = 81, "CC", i = 82, "CD", _
i = 83, "CE", i = 84, "CF", i = 85, "CG", i = 86, "CH", i = 87, "CI", i = 88, "CJ", _
i = 89, "CK", i = 90, "CL", i = 91, "CM", i = 92, "CN", i = 93, "CO", i = 94, "CP", _
i = 95, "CQ", i = 96, "CR", i = 97, "CS", i = 98, "CT", i = 99, "CU", i = 100, "CV", _
i = 101, "CW", i = 102, "CX", i = 103, "CY")
grilla1 = "$" + letra + "$3"
grilla2 = "$" + letra + "$4"
grilla3 = "$" + letra + "$5"
grilla4 = "$" + letra + "$6"
grilla5 = "$" + letra + "$7"
formula1 = "='[Casos_Cerrados_Dic_2009.xls]JUL-AGO'!" & grilla1
formula2 = "='[Casos_Cerrados_Dic_2009.xls]JUL-AGO'!" & grilla2
formula3 = "='[Casos_Cerrados_Dic_2009.xls]JUL-AGO'!" & grilla3
formula4 = "='[Casos_Cerrados_Dic_2009.xls]JUL-AGO'!" & grilla4
formula5 = "='[Casos_Cerrados_Dic_2009.xls]JUL-AGO'!" & grilla5
End If
If opc = 5 Then
letra = Switch(i = 3, "B", i = 4, "C", i = 5, "D", i = 6, "E", i = 7, "F", i = 8, "G", _
i = 9, "H", i = 10, "I", i = 11, "J", i = 12, "K", i = 13, "L", i = 14, "M", i = 15, "N", _
i = 16, "O", i = 17, "P", i = 18, "Q", i = 19, "R", i = 20, "S", i = 21, "T", i = 22, "U", i = 23, _
"V", i = 24, "W", i = 25, "X", i = 26, "Y", i = 27, "Z", i = 28, "AA", i = 29, "AB", i = 30, "AC", _
i = 31, "AD", i = 32, "AE", i = 33, "AF", i = 34, "AG", i = 35, "AH", i = 36, "AI", i = 37, "AJ", _
i = 38, "AK", i = 39, "AL", i = 40, "AM", i = 41, "AN", i = 42, "AO", i = 43, "AP", i = 44, "AQ", _
i = 45, "AR", i = 46, "AS", i = 47, "AT", i = 48, "AU", i = 49, "AV", i = 50, "AW", i = 51, "AX", _
i = 52, "AY", i = 53, "AZ", i = 54, "BA", i = 55, "BB")
grilla1 = "$" + letra + "$3"
grilla2 = "$" + letra + "$4"
grilla3 = "$" + letra + "$5"
grilla4 = "$" + letra + "$6"
grilla5 = "$" + letra + "$7"
formula1 = "='[Casos_Cerrados_Dic_2009.xls]SEP-OCT'!" & grilla1
formula2 = "='[Casos_Cerrados_Dic_2009.xls]SEP-OCT'!" & grilla2
formula3 = "='[Casos_Cerrados_Dic_2009.xls]SEP-OCT'!" & grilla3
formula4 = "='[Casos_Cerrados_Dic_2009.xls]SEP-OCT'!" & grilla4
formula5 = "='[Casos_Cerrados_Dic_2009.xls]SEP-OCT'!" & grilla5
End If
If opc = 6 Then
letra = Switch(i = 3, "B", i = 4, "C", i = 5, "D", i = 6, "E", i = 7, "F", i = 8, "G", _
i = 9, "H", i = 10, "I", i = 11, "J", i = 12, "K", i = 13, "L", i = 14, "M", i = 15, "N", _
i = 16, "O", i = 17, "P", i = 18, "Q", i = 19, "R", i = 20, "S")
grilla1 = "$" + letra + "$3"
grilla2 = "$" + letra + "$4"
grilla3 = "$" + letra + "$5"
grilla4 = "$" + letra + "$6"
grilla5 = "$" + letra + "$7"
formula1 = "='[Casos_Cerrados_Dic_2009.xls]NOV-DIC'!" & grilla1
formula2 = "='[Casos_Cerrados_Dic_2009.xls]NOV-DIC'!" & grilla2
formula3 = "='[Casos_Cerrados_Dic_2009.xls]NOV-DIC'!" & grilla3
formula4 = "='[Casos_Cerrados_Dic_2009.xls]NOV-DIC'!" & grilla4
formula5 = "='[Casos_Cerrados_Dic_2009.xls]nOV-DIC'!" & grilla5
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'-----LLENA LAS ENCUESTAS DEPENDIENDO DE LA RESPUESTA ES DONDE PONE LA RESPUESTA----------------
Range("A1").Select
ActiveCell.formula = formula1
valor = ActiveCell.Value
ActiveCell.formula = ""
celda = Switch(valor = "E", "C15", valor = "MB", "E15", valor = "B", "G15", valor = "R", "I15", valor = "D", "K15")
Range(celda).Select
ActiveCell.formula = formula1
Range("A1").Select
ActiveCell.formula = formula2
valor = ActiveCell.Value
ActiveCell.formula = ""
celda = Switch(valor = "E", "C20", valor = "MB", "E20", valor = "B", "G20", valor = "R", "I20", valor = "D", "K20")
Range(celda).Select
ActiveCell.formula = formula2
Range("A1").Select
ActiveCell.formula = formula3
valor = ActiveCell.Value
ActiveCell.formula = ""
celda = Switch(valor = "E", "C25", valor = "MB", "E25", valor = "B", "G25", valor = "R", "I25", valor = "D", "K25")
Range(celda).Select
ActiveCell.formula = formula3
Range("A1").Select
ActiveCell.formula = formula4
valor = ActiveCell.Value
ActiveCell.formula = ""
celda = Switch(valor = "E", "C30", valor = "MB", "E30", valor = "B", "G30", valor = "R", "I30", valor = "D", "K30")
Range(celda).Select
ActiveCell.formula = formula4
Range("A1").Select
ActiveCell.formula = formula5
valor = ActiveCell.Value
ActiveCell.formula = ""
celda = Switch(valor = "E", "C35", valor = "MB", "E35", valor = "B", "G35", valor = "R", "I35", valor = "D", "K35")
Range(celda).Select
ActiveCell.formula = formula5
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'---------------------------------------------------------
'LIMPIA LAS CELDAS DONDE VAN LAS RESPUESTAS PARA SU POSTERIOR LLENADO CORRESPONDIENTE
Sub Limpiar(NuevaHoja As String)
Sheets(NuevaHoja).Select
Dim celda As String
Dim numero As Integer, cont As Integer, row As Integer
Dim Col As String
row = 15
Col = "C"
While row < 36 ' 15 20 25 30 35
celda = Col + CStr(row)
Range(celda).Select
ActiveCell.FormulaR1C1 = ""
row = row + 5
Wend
Col = "E"
row = 15
While row < 36 ' 15 20 25 30 35
celda = Col + CStr(row)
Range(celda).Select
ActiveCell.FormulaR1C1 = ""
row = row + 5
Wend
Col = "G"
row = 15
While row < 36 ' 15 20 25 30 35
celda = Col + CStr(row)
Range(celda).Select
ActiveCell.FormulaR1C1 = ""
row = row + 5
Wend
Col = "I"
row = 15
While row < 36 ' 15 20 25 30 35
celda = Col + CStr(row)
Range(celda).Select
ActiveCell.FormulaR1C1 = ""
row = row + 5
Wend
Col = "K"
row = 15
While row < 36 ' 15 20 25 30 35
celda = Col + CStr(row)
Range(celda).Select
ActiveCell.FormulaR1C1 = ""
row = row + 5
Wend
End Sub
Como veran es un codigo extenso aunque muy repetitivo, no supe como disminuior el codigo, tal vez despues de hacer las graficas desde vba lo intente, debo decir que no le se mucho al vba, de hecho no he usado visual basic jeje. |