martes, 8 de febrero de 2011

VBA:Insertar una función con macros.

Un lector enviaba una cuestión sobre la manera de insertar una función en nuestra hoja de cálculo a través de una macro:

...es posible y como puedo cambiar en una formula el valor absoluto de la dirección de una celda por una variable, me explico:
Si tengo la formula ActiveCell.Formula = "=SUBTOTAL(9,I3,I500)" , resulta que quiero cambiar en el valor I500 , el 500 por el valor que traiga una variable, (I&variable) si variable tiene el valor de 501, quedaría I501, me funciona en los rangos la siguiente formula Range("A" & variable).Select, pero en la anterior formula no he podido cambiar el 500 por el valor de una variable....


Efectivamente es posible incluir funciones o fórmulas en nuestras hojas de cálculo, a través de una macro, de esta manera tan directa. Lo único que hacemos realmente es determinar que queremos que se escriba en la celda deseada; todo lo que vaya entrecomillado se añadirá simplemente en esa celda elegida.
Si tenemos claro esta clave, será fácil responder a nuestro amigo. La línea de código que soluciona su problema sería:
ActiveCell.Formula = "=SUBTOTAL(9,I3:I" & variable & ")"


Desarrollémoslo con un ejemplo muy sencillo. Disponemos en nuestra hoja de cálculo de un listado de importes en el rango A1:A11, aunque sabemos que éste variará según crezca nuestra base de datos. Queremos conocer en cada momento cuál es la suma del rango vivo con el que trabajamos, en la celda C1.
Nuestros valores actuales:


el código VBA de nuestra macro:

'macro que calcula la suma de todas las entradas de datos para el rango vivo A1:A??.
Sub introducir_formula()
'con UsedRange determinamos el rango empleado actual en nuestra hoja de calculo.
'con .Rows.Count conocemos el total de filas del rango.
ultfil = ActiveSheet.UsedRange.Rows.Count

'¡¡cuidado ya que las funciones incluidas deben ir en Inglés!!
Range("C1").Formula = "=SUM(A2:A" & ultfil & ")"
End Sub


Muy importante, no olvidar que las funciones empleadas dentro de este código deben aparecer escritas como en su versión en inglés!!!; ya que si no nos devolvería un error de #¿NOMBRE?.

Al emplear, en este caso .UsedRange nos debemos asegurar que no existe ningún otro valor de en la hoja de cálculo, además de los necesarios para la suma. Si fuera así, tendríamos que emplear alguna otra instrucción o variable para determinar cual es la última celda de nuestro rango.

190 comentarios:

  1. no me funciona el comando.
    quiero usar la formula

    ActiveCell.FormulaR1C1 = "=STDEV.S("R"&e&"C[-12]":"R"&f&"C[-12]")"

    y me sale error trabajo en excel 2010

    ResponderEliminar
  2. Hola, ¿que tal?
    supongo que 'e' y 'f' son dos variables que has definido y devuelve alguna localización del rango de datos sobre el que quieres trabajar.
    Como estás trabajando con el sistema RC, tu función debería incluir (ya que parece ser que tu rango de datos está en diferentes filas) los corchetes también a la R.
    Algo así:
    ActiveCell.FormulaR1C1 = "=STDEV.S(R["&e&"]C[-12]:R["&f&"]C[-12]")"
    si las variables e,f están bien definidas debería funcionar.
    Un saludo

    ResponderEliminar
  3. Para insertar las fórmulas en español (o en el idioma en el que se tenga instalado el Excel) se debe utilizar Range("C1").FormulaLocal en vez de Formula y listo. De hecho existe una forma de traducir fórmulas utilizando Formula (en inglés) y FormulaLocal (en el idioma de la instancia de Excel). Saludos.

    ResponderEliminar
  4. Otra forma de solucionar el problema que comenta el lector, sin macros, sería utilizar la función de Excel INDIRECTO. Pondríamos en una celda, por ejemplo D1, el número que deseemos y así nos quedaría:
    =SUBTOTAL(9, I3, INDIRECTO("I" & D1))

    Haced la prueba poniendo en una celda INDIRECTO("I" & D1) y veréis el contenido de la celda correspondiente variando D1.

    ResponderEliminar
  5. Muchas gracias por los aportes ELF,
    siempre es bienvenida información útil FormulaLocal, lo tendré en cuenta en el futuro...
    De la función INDIRECTO ya he subido en alguna ocasión comentarios o ejemplos.
    Un saludo!!

    ResponderEliminar
  6. Como puedo hacer que el resultado de subtotales de una hoja, pueda pasarlos a otra hoja si estos subtotales cumplen una condicion por ejemplo si el subtotal es mayo a una cantidad x, como puedo hacerlo con vba gracias

    ResponderEliminar
  7. Hola Manuel,
    tendrías que definir una función
    Application.WorksheetFunction.Subtotal
    en tu macro, y condicionarla con un IF...THEN
    para situarla en la celda de la hoja que quisieras.
    Algo así
    IF Application.WorksheetFunction.Subtotal(...)>x THEN Sheet("Hoja1").Range("A1").Value=Application.WorksheetFunction.Subtotal(...)

    Espero te oriente.
    Un saludo

    ResponderEliminar
  8. me pueden ayudar? como evito en excel vba que me aparesca NO RESPONDE al
    correr una apilicacion.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Rene,
      es difícil sin ver el error concreto, pero probablemente el código haya entrada en un bucle, o directamente hay una parte de la programación incorrecta.
      Yo probaría a ir deshaciendo las últimas líneas agregadas, hasta dar con el error...
      Poco más puedo comentar, lo siento.
      Slds

      Eliminar
    2. se soluciono colocando un DoEvents dentro del ciclo largo y con esto ya tengo el control en cualquier momento. Gracias por la ayuda

      Eliminar
  9. buenas yo intento meter un macro dentro de una formula es decir

    tengo esta formula: SI(N6<>0;DACUM(N6;DCOD(C6);R6);"")

    con DCOD(c6) intento llamar a esta macro:

    Function DCOD(MiNum)
    DCOD = Application.WorksheetFunction.Lookup("MiNum,CODIGOS!C6:E31,CODIGOS!E6:E31")
    End Function

    pero no me funciona no se porque no me funciona, estoy llamando los datos de otra hoja que se llama codigos

    me ayudas? ah DACUM es una function que hice si quito el DCOD de la formula funciona bien

    ResponderEliminar
    Respuestas
    1. Hola fofo,
      parece que la función LookUp no está bien construida, esta es la equivalente a la estándar de la hoja de cálculo, BUSCARV, por lo que requiere de los mismos argumentos, debería ser algo del estilo:
      Application.WorksheetFunction.Lookup(MiNum,CODIGOS!C6:E31,indicador columna)
      es decir, estás asignando un indicador de columna incorrecto, señala cuál es el valor correspondiente, en qué columna de la matriz C6:E31, se encuentra el valor... y te debería funcionar.
      Slds y ya me cuentas

      Eliminar
  10. ¿Como puedo hacer una formula, agregada en macro para calcular el ispt?

    ResponderEliminar
    Respuestas
    1. Hola buenos días,
      lo primero sería conocer qué es el ispt (ahora mismo no caigo), y después conocer los operadores y parámetros de dicha función...
      Slds

      Eliminar
  11. Hola:
    Espero que me permitais hacer una pregunta, y si ademas, me la podeis contestar.....
    Llevo un par de dias jugando con una pagina de excel que encontre por ahi y que me crea un calendario; la he ido adaptando, y el problema que tengo, es que cuando tengo error en alguna macro, en lugar de informarlo, se limita a salir de golde de la macro, funcion, o lo que sea sin decir nadda; eso me obliga a depurar para ver donde sale, y me encuentro con que sale en funciones como:
    ActiveSheet.PageSetup.PrintArea = ""

    o
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$30"
    Y no tengo ni idea el porque...

    ResponderEliminar
    Respuestas
    1. Pues sin saber cómo es la macro entera ni idea. De todas formas prueba a utilizar la captura de errores con "On error".

      Eliminar
    2. Hola Miguel,
      como dice Efrén, lo mejor para darte una opinión sería conocer la macro completa, por si acaso, hay alguna incongruencia en la programación...
      En principio, se podría emplear la instrucción on error resume next que comenta Efrén, pero eso lo único que te solucionaría sería evitar el error, no solucionarlo.
      Es algo raro que se pare/falle en algo tan sencillo como:
      ActiveSheet.PageSetup.PrintArea = ""

      o
      ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$30"

      que parece es correcto.
      De todas formas, si quieres un calendario probado, hace unos días subí uno en el blog:
      http://excelforo.blogspot.com.es/2012/06/vba-macro-para-conseguir-un-calendario.html
      quizá te sirva para comparar o quizá te guste más...
      Un saludo

      Eliminar
    3. Este comentario ha sido eliminado por el autor.

      Eliminar
  12. Efrén y ExcelForo:
    Gracias por contestar, No envie el codigo porque pense que no era importante; el de la macro que ahora falla es este
    Sub imprimir_calendario()
    On Error GoTo Errhandler
    ActiveSheet.PageSetup.PrintArea = ""
    'Seleccionamos el área para imprimir
    Sheets("Calendario").Activate

    Range("A1:AF30").Select

    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$30"
    'definimos las preferencias de impresión
    With ActiveSheet.PageSetup
    'Centramos la impresión
    .CenterHorizontally = True
    .CenterVertically = True
    'Ajustamos la impresión a 1 página
    'tanto de alto como de ancho
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .Orientation = 2
    End With
    'Imprimimos el calendario
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Exit Sub

    Errhandler:

    ' Run the ErrorHandling macro to display the error and to
    ' return a value for Action which will determine the appropriate
    ' action to take (Resume the macro or end the macro)
    Dim Action As Integer
    ErrorHandling Err, Action

    If Action = Err_Exit Then
    Exit Sub
    ElseIf Action = Err_Resume Then
    Resume
    Else
    Resume Next
    End If
    End Sub

    El tratamiento de errores lo he añadido posteriormente para ver si me indicaba algo; daros cuenta que el problema es que no da ningun mensaje de error, pero la macro deja de ejecutarse en las lineas que os indicaba.

    Tengo previsto compartir el codigo con todos, ya que una de las cosas que hago es dar formacion excel... Si quereis que lo suba, me decis a donde y os lo envio

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      acabo de comprobar tu rutina y a mi me funciona (desactivando, claro está la llamada a las macros 'ErrorHandling'), no me falla en la configuración de área de impresión...
      ¿que versión de Excel tienes??... No recuerdo ahora mismo el código configuración de página en Excel 2003, pero quizá por ahí vaya el problema???
      Slds

      Eliminar
    2. Estoy utilizando 2007, y si me dices donde, te envio el fichero completito

      Eliminar
    3. Envíamelo a
      excelforo@gmail.com

      Slds

      Eliminar
  13. Dos cosas mas:
    Disculpad el duplicado; desapareció y lo volví a picar
    El tratamiento de errores lo añadí después de que no me diera ningún aviso, para ver si así, lo daba.

    ResponderEliminar
  14. yo puedo insertar una función en una columna, pero que no quede como si estuviese llena o con datos.
    me esplico el codigo que hice para agregar datos me identifica la ultima fila sin datos y ahi crea mi producto
    o cliente, en la primera columna concatene como 6 columnas.
    debo aplicar esa funcion a toda la columna pero si hago eso me toma como que estan llenas y el dato lo mete
    al final o no lo carga, pierde la continuidad por asi decirlo.

    este es el codigo para crear el producto

    Private Sub cmdcrearproducto_Click()

    'Dim descripciontotal As String
    Dim producto As String
    Dim presentacion As String
    Dim marca As String
    Dim iva As String
    Dim lote As String
    Dim existencias As String

    Dim ultimafila As Double

    producto = TextBox1.value
    presentacion = TextBox2.value
    marca = TextBox3.value
    iva = TextBox6.value
    lote = TextBox4.value
    existencias = TextBox5.value

    ultimafila = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

    Cells(ultimafila + 1, 2) = producto
    Cells(ultimafila + 1, 3) = presentacion
    Cells(ultimafila + 1, 4) = marca
    Cells(ultimafila + 1, 5) = iva
    Cells(ultimafila + 1, 6) = lote
    Cells(ultimafila + 1, 7) = existencias


    TextBox1.value = ""
    TextBox2.value = ""
    TextBox3.value = ""
    TextBox4.value = ""
    TextBox5.value = ""
    TextBox6.value = ""


    Unload FRMCREARPRODUCTO

    ActiveWorkbook.Save

    MsgBox ("El producto ha sido creado.")

    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      veo que adjuntas el código de acción, con el evento clik para un CommandButton (supongo), que recoge los valores de 6 cuadros de texto (TextBox) y los lleva a la última fila de la hoja activa, a las columnas B, C, D, E, F y G. Finalmente dejas limpios los TextBox, descargas el formulario (que supongo contiene estos cuadros de texto y botones).
      Lo que no veo es a lo que te refieres de la columna A y de esa fórmula o función que mencionas.
      Si fueras algo más específico en este sentido, quizá pueda orientarte algo más.
      Slds

      Eliminar
  15. hola, soy isidro me encanto este trabajo para mis estudiantes, aunque yo les caiga mal completaron su trabajo gracias a esta imformacion.

    ResponderEliminar
    Respuestas
    1. Ja, ja...
      me alegra fue útil para alguien.
      Un cordial saludo

      Eliminar
  16. HOLA POR FAVOR AYUDA, RESULTA QUE NECESITO CREAR POR MEDIO DE VISUAL BASIC EN EXCELL LA SIGUIENTE FORMULA:
    =SUMAR.SI($A$1:$A$1000,B1,$C$1:$C$1000)
    AQUI MI PROBLEMA ES QUE NECESITO QUE B1 VAYA CAMBIANDO CONFORME VAYA ANEXANDO FORMULAS (B2,B3,ETC), ADEMAS ME ARROJA EL ERROR .
    MIL GRACIAS SI ALGUIEN ME PUEDE AYUDAR

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías emplear una variable que detecte cuál es la última celda de la columna B:
      variable: Range(Rows.Count, "B").End(xlup).Offset(1,0)
      recuerda que si vas a utilizar el mismo método explicado en la entrada del blog, deberás usar la función en su forma inglesa, en tu caso SUMIF.
      Slds

      P.D.: Por favor, no escribas en mayúsculas.

      Eliminar
  17. Buenas Tardes, Mi nombre es Edgar, de favor una consulta.
    He creado algunos userform en visual Basic, pero en la compu que manejo tiene excel 2000 y todo bien.
    La situación es que al abrir el archivo en otra máquina que tiene excel 2007 simplemente no se muestra la carpeta de formularios ni de módulos como si nunca hubiera existido.
    si de favor me pueden apoyar con este trabajo que tengo que hacer ya que tuve que volver a generar los userform
    Mil Gracias

    ResponderEliminar
    Respuestas
    1. Hola Edgar,
      la verdad que nunca trabajé con macros en una versión tan antigüa, pero hasta donde yo sé, las versiones anteriores son totalmente compatibles, y no deberías tener problemas como el que indicas.
      Sí a la inversa (pasar de 2007 a 2000), pero nunca algo similar a que desaparezcan los módulos o formularios.
      No tengo instalada esa versión de Excel 2000, por lo que no puedo asegurartelo.

      Supongo, por descartar cosas sencillas, que has mostrado todo el Proyecto en el Editor, esto es, que no te queda nada oculto, sin desplegar...

      En último caso podrías simplemente abrir el libro de Excel 2000 y otro de 2007, e ir copiando y pegando o exportanto/importando los diferentes módulos o formularios.

      Siento no poder ayudarte con tu incompatibilidad.
      Suerte!!

      Eliminar
  18. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  19. saludos a toda la comunidad de Excelforo, mi nombre es Irving, me podrían por favor ayudar es que he tenido problemas con una consulta, lo que quiero es que de manera dinámica sumar cada columna los valores agregados para así poner el resultado de la suma en la primea celda vacía de dicha columna, mi objetivo es hacer varias sumas en la misma columna.
    este es mi Código:

    Dim cell As Range
    Private Sub CommandButton1_Click()
    'Worksheets("Hoja1").Activate
    For Each cell In ActiveCell.CurrentRegion.Cells
    sum = sum + cell.Value
    Next
    Cells(Range("B1").End(xlToRight).Row + 1, 1) = sum
    End Sub

    He tenido problemas con el CurrentRegion pues el resultado de la suma lo coloca en la celda A2.

    saludos y muchas gracias por su tiempo.

    ResponderEliminar
    Respuestas
    1. Hola Irving,
      no creo que te pueda servir CurrentRegion, ya que por lo que entiendo quieres un acumulado por cada columna, y empleando tu código obtendrías, en todo caso, un único acumulado de todas las columnas en una sóla celda.
      Tendrías que hacer primero un recorrido por la cabecera, y un segundo como el que tienes recorriendo celdas por filas, hasta acabar incorporando el acumulado.

      Podría ser algo así:
      Private Sub CommandButton1_Click()
      Dim celda As Range

      Worksheets("Hoja6").Activate
      Range("A1").Select

      Set rng = ActiveCell.CurrentRegion
      For i = 1 To Range(rng.Address).Columns.Count
      Sum = 0
      Set rngsuma = Range(Cells(1, i).Address, Cells(1, i).End(xlDown).Address)
      MsgBox rngsuma.Address
      For Each celda In Range(rngsuma.Address)
      Sum = Sum + celda.Value
      Next celda
      Cells(Cells(1, i).End(xlDown).Row + 1, i).Value = Sum
      Next i
      End Sub

      Espero te sirva.
      Slds

      Eliminar
    2. Hola Ante todo Muchísimas gracias por la respuesta me aclaraste muchas dudas con respecto al código
      tengo otra duda que espero no molestar, y es que quiero poner varias sumas en la misma columna como por ejemplo algo asi la suma $A$1:$A$5: 100, $A$8:$A$12: 50, etc para asi hacer un desglose de sumas en la misma columna.

      Sin mas
      Muchas gracias por tu atención
      saludos

      Eliminar
    3. Hola Irvin,
      habría que saber cuántos rangos quieres sumar en la columna, ya que el trabajo de contruir una macro que reconozca los diferentes rangos de celdas a sumar puede superar el trabajo manual de incorporar la función SUMA para cada uno de ellos...
      Slds

      Eliminar
  20. Hola Muy buenos días, Soy Edgar de Acapulco, Mexico. Por favor si pudieran ayudarme, la verdad gracias a que se haya creado esta página que nos ha ayudado a muchos.
    Mi situacion es que tengo una hoja con datos desordenados y algunas filas tienen borde superior y varias no.
    Al ordenar las filas de la A-Z se ordenan los datos correctamente, solo que los bordes no se mueven junto con los datos. ya he intentado de varias formas. Les agradezco mucho su ayuda si es que existe una solución.

    ResponderEliminar
    Respuestas
    1. Hola Edgar!!
      bueno, hasta donde yo sé no es posible, ya que la ordenación sólo permite de manera diferenciada ordenar o bien por Valores o bien por Color (de fuente o de fondo de celda) o bien si hubiera algún icono, esto significa que si ordenas por valores (por ejemplo) mantendría los formatos de Borde (no así los de fondos o fuente).... hablamos siempre de un formato 'estándar'.
      Ahora la solución. No sé si será posible.
      Si asignas un Formato condicional a todo el rango a ordenar, definiendo una Regla que asigne esos Bordes a las celdas precisas, después de la ordenación conseguirás que esas celdas, estén donde estén, te aparezcan con los Bordes.

      Espero haberme explicado, si no es así, dímelo ;-)
      Slds

      Eliminar
  21. Hola, de verdad que eres un genio con Excel, Soy Edgar y me funcionó perfectamente el usar el formato condicional para ordenar las filas y que se movieran tambien los bordes.
    Mil Gracias

    ResponderEliminar
  22. Hola Buenas tardes, quiero ver si de favor me ayudan con lo siguiente:
    He creado un Userform que contiene 2 ListBox, cada una con varias opciones , aparte un Comandbutton que ingresa en la opcion del list box seleccionado.
    Quiero ver si hay un comando que cuando seleccione por error una opcion de cada listbox al mismo tiempo (esto lo permite), me envíe un un mensaje y no permita accesar a ninguno, ya que actualmente ingresa en la primera seleccion, lo aplica, pero al salir, inmediatamente corre la segunda opcion seleccionada.
    Mil Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      por lo que entiendo, probablemente sea suficiente englobar ambos ListBox dentro de un mismo Marco (Frame), esto evita en general que queden marcadas dos opciones de diferentes controles que estén en el mismo Frame.
      Algo similar en
      http://excelforo.blogspot.com.es/2011/05/vbaconfigurar-un-grafico-usando-botones.html

      Espero te pueda servir.
      Slds

      Eliminar
  23. Hola,
    Mi nombre es Jaime Wilchez, Tengo la siguiente macro con la que establezco una conexion a una base de datos SQL y recupero ciertos datos:
    Sub Ejecutar()
    'Se crea la Conexión a la Base de Datos
    Dim ZCadenaConexion As String
    ZCadenaConexion = "Provider=SQLOLEDB;Data Source=SERVIDORSQL;Initial Catalog=BASE1;Integrated Security=SSPI"

    Dim ZConexion As ADODB.Connection
    Set ZConexion = New ADODB.Connection

    ZConexion.ConnectionString = ZCadenaConexion
    ZConexion.Open (ZCadenaConexion)

    'Se crea el Set de Datos donde se guarda el resultado de la Consulta
    Dim ZDatos As ADODB.Recordset
    Set ZDatos = New ADODB.Recordset
    ZDatos.ActiveConnection = ZConexion
    ZDatos.Open "select (select SUM(currtotal)from OACT where LEFT(acctcode,4) in ('1105','1110'))"

    'Se crea el Set de Datos donde se guarda el resultado de la Consulta
    Dim YDatos As ADODB.Recordset
    Set YDatos = New ADODB.Recordset
    YDatos.ActiveConnection = ZConexion
    YDatos.Open "select (select SUM(currtotal)from OACT where LEFT(acctcode,4) in ('1305','1390','139905'))"

    'Se copia el contenido del Set de Datos a la celda A1 de la Hoja1
    Hoja1.Range("A2").CopyFromRecordset ZDatos
    Hoja1.Range("A3").CopyFromRecordset YDatos

    'Se cierra el Set de Datos y la Conexión a la Base de Datos
    ZDatos.Close
    ZConexion.Close
    End Sub

    No se si en excel se pueda Pasar una celda de excel como un parametro, para que yo al momento de ingresar una fecha en una celda y ejecutar la macro use ese valor de esa celda y me lo coloque en la consulta de SQL y traer los datos con ese nuevo parametro.

    De antemano muchas gracias por su atencion

    ResponderEliminar
    Respuestas
    1. Hola Jaime,
      si, claro, sólo define una variable para el valor de esa celda.
      Por ejemplo, el tipo de dato de la celda A1 es 'texto', asi que haríamos:
      Dim strVariable as String
      strVariable=range("A1").Value
      'a continuación usas la variable definido 'strVariable
      donde la necesites en tu código...

      Slds

      Eliminar
    2. De nuevo yo Jaime Wilchez,

      Muchas gracias por tu respuesta me sirvio de mucho, ahora viene otra inquietud. Tengo la siguiente consulta:
      "select SUM(Debit - Credit) from JDT1 where LEFT(Account,4) in ('1105','1110') And RefDate in " & "(" & " '" & Format(Worksheets("Hoja1").Range("A1").Value, "YYYY-MM-DD") & "'" & "," & "'" & Format(Worksheets("Hoja1").Range("B1").Value, "YYYY-MM-DD") & "'" & ")"
      El problema es que necesito cambiar clausula In que tiene la columna "Refdate" por la clausula Between, como podria hacer eso, si al momento de concatenar el operador logico And que necesita la clausula me dice que no los puedo unir.

      Muchas gracias su pueden ayudarme

      Eliminar
    3. Hola Jaime...
      ¿me estás hablando de una consunta (query) realizada con Ms-Query?¿O con Access?...
      En todo caso intentaría modificarla con cualquiera de esas dos aplicaciones.
      Slds

      Eliminar
  24. Ok... ya pude hacer la consulta... la genere abriendo un objeto de conexion en vba.

    Muchas gracias por su colaboracion

    ResponderEliminar
  25. Hola,
    Quiero hacer una función que tiene dos argumentos, los pegue en dos celdas de otra hoja, y devuelva el valor de una tercera celda de esa hoja, la cual está en función de los parametros que hemos pegado. Me puedes ayudar?

    ResponderEliminar
    Respuestas
    1. Hola,
      lo que comentas tiene pinta de ser una macro (procedimiento Sub) más que una Function. Algo de este estilo
      Sub probando()
      Dim valor1 As Long, valor2 As Long
      valor1 = InputBox("valor1?")
      valor2 = InputBox("valor2?")
      Sheets("Hoja1").Range("A117").Value = valor1
      Sheets("Hoja1").Range("A118").Value = valor2

      Sheets("Hoja10").Range("A120").Value = valor1 + valor2

      End Sub

      Siendo valor1 y valor 2 los 'parámetros', llevando dichos parámetros a la Hoja1 y el resultado a la hoja 10.

      Espero te oriente
      Slds

      Eliminar
  26. Hola, Ismael

    Soy IVAN el de la consulta (Anónimo abril 29, 2013). Probé la macro como me lo recomendaste y funcionó, muchas gracias. Deseo consultarte si es posible anidar dicha macro dentro de una función personalizada de tal modo que pueda usar dicha función en cualquier parte del documento y que pueda, inclusive, copiarla arrastrandola.

    En la hoja 1 tengo datos en las columnas "A" y "B" y en la columna "C" esta la función personalizada

    A1 B1 =miformula(A1, B1)
    A2 B2 =miformula(A2, B2)
    A3 B3 =miformula(A3, B3)

    En la hoja 2 tengo dos casillas D1 y E1. En una tercera casilla F1 tengo el resultado de una serie de operaciones que dependen de los valores de D1 y E1. En otras palabras la Hoja 2 actuaría como una caja negra donde entran dos valores y sale uno.

    El objetivo de mi función personalizada es llevar los dos argumnetos a la Hoja 2 y traerme el resultado.

    Es posible crear esta función? y cómo sería?

    Iván



    ResponderEliminar
    Respuestas
    1. Hola Ivan,
      envíame el fichero con la estructura de datos que tengas y la operación de la Hoja2.
      ¿Siempre se calcula en la hoja2 en las mismas celdas D1 y E1 calculándose en F1???
      Por que no haces la fórmula directamente en la hoja de cálculo Hoja1??
      Si quieres envíame a
      excelforo@gmail.com
      el fichero con el detalle concreto.
      Slds

      Eliminar
  27. saludos foro, soy nuevo en esto y veo mucha clase en los comentarios. necesito saber si me pueden ayudar con algo: tengo una lista de nombres de personas en una columna y quiero al hacer click en una celda, que esa lista se cargue completa en otra columna como hago. gracias.

    ResponderEliminar
    Respuestas
    1. Hola Elegebr,
      lo que podrías realizar (parece) es un sencillo copiado y pegado de esa columna, ver cómo
      Para luego asociar esa macro construida a un botón, o bien al seleccionar una celda concreta (en este caso deberás emplear un evento selectionchange junto al métdo intersect (ver

      Slds cordiales

      Eliminar
  28. Hola!!!
    Alguién me podria ayudar
    En una hoja de Excel, necesito contar dentro de una variable la opción: si, Si y no,
    La pregunta es: como puedo hacer para que me cuente si y Si como un único si y no como dos (si y Si)

    ResponderEliminar
    Respuestas
    1. Hola,
      si empleas la función CONTAR.SI responderá como lo necesitas, es decir, si en un rango B2:B9 tienes los valores si, Si, SI, no, con la función
      =CONTAR.SI(B2:B9;"si")
      te contará, sin discriminar mayúsculas de minúsculas.
      Slds

      Eliminar
  29. Estoy tratando de montar una base de datos de planilla de salarios introduciendo los datos desde un formulario, y lo que quiero es Totalice al final de cada columna, pero la la hora de actualizar, necesito que me borre este total por que le haría es que se acumularía falseando los datos. Osea totalizarlo y borrarlo cuando yo quiera.

    'macro que calcula la suma de todas las entradas de datos para el rango vivo A1:A??.
    Sub introducir_formula()
    'con UsedRange determinamos el rango empleado actual en nuestra hoja de calculo.
    'con .Rows.Count conocemos el total de filas del rango.
    ultfil = ActiveSheet.UsedRange.Rows.Count

    '¡¡cuidado ya que las funciones incluidas deben ir en Inglés!!
    Range("d1").Formula = "=SUM(A2:A" & ultfil & ")"
    Range("e1").Formula = "=SUM(b2:b" & ultfil & ")"
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Erci, que tal, cómom estás?
      Espero te encuntres bien.
      No sé si te refieres a que trabajas con un UserForm para introducir ciertos valores o que simplemente trabajas sobre tu hoja de cálculo, replicando alguna plantilla.
      En todo caso para borrar ese Subtotal que añades con la propiedad .Formula lo puedes borrar con la propiedad .Clear
      Range("D1").Clear

      Saludos cordiales

      Eliminar
  30. POr donde enmpiezo con las macros ???

    ResponderEliminar
    Respuestas
    1. Hola, que tal
      Espero te encuentres bien.

      Si nunca has tocado macros ni has visto lenguajes de programación, quizá te convendría empezar con una visión general, leyendo algún manual sencillo o con un curso.
      Yo ofrezco cursos online,conmigo como tutor; puedes ver algo más en
      http://www.excelforo.com/macros_iniciacion.htm

      Slds cordiales

      Eliminar
  31. Hola a todos!

    quiero hacer un layout para nomina desde una hoja de excel, mi programacion esta asi:

    Public Sub CommandButton1_Click()
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim e As Double
    Dim f As Double

    a = Application.WorksheetFunction.Sum(Range("k8:k1000"))
    b = Application.WorksheetFunction.Sum(Range("l8:l1000"))
    c = Application.WorksheetFunction.Sum(Range("N8:N1000"))
    e = Application.WorksheetFunction.Sum(Range("O8:O1000"))
    f = Application.WorksheetFunction.Sum(Range("P8:P1000"))

    Label2 = a
    Label3 = b
    Label5 = c
    Label8 = e
    Label7 = f
    Label12 = a + b - c - e - f

    End Sub

    el problema que tengo es que suma toda la fila, peri si meto filtros me sigue sumando todo, como puedo hacer para que solo me sume lo del filtro.

    ResponderEliminar
    Respuestas
    1. Hola,
      realmente no veo necesario una macro para realizar ese cálculo, y más cuando empleas la función SUMA de la hoja de cálculo...
      En todo caso, para SUMAR rangos filtrados la función a emplear es la de SUBTOTALES.
      Echa un vistazo a
      http://excelforo.blogspot.com.es/2011/11/la-funcion-subtotales-en-tablas-de.html

      Slds

      Eliminar
  32. Buenas reciban un cordial saludo, he realizado la siguiente macro para estimar una regresion lineal multiple tres parametros b1, b2 y b3 cuyos resultados apareceran en las celdas c5:e5; el rango de la variable dependiente es c5:c11 y el rango de las variables independientes es d5:e11. La macro ha quedado definida asi:
    Range("C3:E3").Select
    Selection.FormulaArray = "=LINEST(R[2]C:R[8]C,R[2]C[1]:R[8]C[2],TRUE,FALSE)"
    el problema esta en que necesito que los rangos de las variables dependiente e independiente se puedan ampliar o reducir según tenga mas o menos datos como en ralidad me sucede, necesito estimar todos los días los rendimientos de unos instrumentos financieros pero la cantidad de instrumentos que aparecen por día es variable. Si me pueden ayudar se los agradecería muchisimo.
    Gracias de antemano, saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      si quieres emplear macros sigue las indicaciones de esta misma entrada para definir cuál es la última fila de datos, e incorporarlo en modo de variable dentro de la fórmula.
      Si bien si sólo quieres calcular la ESTIMACION.LINEAL puedes hacerlo directamente en la hoja de cálculo trabajando con Nombres definidos sobre el origen de datos, que deberá ser convertido en una Tabla, de este modo los Nombres definidos (que se emplearían como argumentos de esa función) se adaptarán al número de registros de las variables dependiente e independiente.
      Slds cordiales

      Eliminar
  33. Disculpen pero es que no soy muy diestro en el uso de las macros, hice la siguiente sentencia pra crear una variable que seleccione el rango con la ultima celda ocupada con datos, tanto para la variable dependiente como la independiente en la regresión pero no me resulta, no se dónde está el error
    Range("C3:E3").Select
    d = Range("c5").End(xlDown).Select
    i = Range("d5:e5").End(xlDown).Select
    Range("C3:E3").Select
    Selection.FormulaArray = "=LINEST(d,i,TRUE,FALSE))"
    Si me pueden ilustrar con un ejemplo cómo hacerlo se los agradecería muchisimo.

    saludos cordiales y gracias por su aporte.

    ResponderEliminar
    Respuestas
    1. Hola de nuevo,
      suponiendo que en A2:A... está la variable independiente y en B2:C... las variables independientes, puedes probar con:

      Set d = Range("A2", Range("A2").End(xlDown))
      Set i = Range("B2", Range("B2").End(xlDown).Offset(0, 1))
      Range("D3:F3").FormulaArray = "=LINEST(" & d.Address & "," & i.Address & ",TRUE,FALSE)"

      Slds

      Eliminar
  34. Acabo de probar lo que me enviaste y funciona perfectamente, es lo que estaba buscando, de verdad muchísimas gracias por tu apoyo y paciencia y felicidades por el foro, en verdad es una gran ayuda para aquellos como yo que nos estamos iniciando en este mundo algo complicado pero interesante y de mucha utilidad como los son las macros. Nuevamente gracias.

    ResponderEliminar
  35. Disculpa nuevamente, pero estoy intentando que las variables independientes de mi modelo se calculen automaticamente para luego estimar la regresión, diariamente solo voy a contar con valores en las dos primeras columnas de las tabla, las variables independientes van a estar en las columnas 3 y 4 de la tabla y estaran en función de los valores de la columna 1 y una constante. Hice la siguiente macro previa a la que tu me recomendaste para la estimación lineal. Cree dos variables siguiendo tu ejemplo (b y c) y luego las utilizo para calcular la formula para cada variable independiente, pero al correr la macro me sale como resultado en cada celda la palabra FALSO. No se cuál es el problema, o si esta realizando bien la estimación. Por supuesto la regresion lineal no se estima ya que no hay valores numericos.
    Gracias de antemano. Saludos Cordiales.
    Set b = Range("b8", Range("b8").End(xlDown))
    ufila = Range("b" & Rows.Count).End(xlUp).Row
    For i = 8 To ufila
    Cells(i, 4) = Formula = "=(1-exp(-" & b.Address & "/$b$3))*$b$3/" & b.Address & ""
    Next
    Set c = Range("b8", Range("b8").End(xlDown))
    ufila = Range("c" & Rows.Count).End(xlUp).Row
    For i = 8 To ufila
    Cells(i, 5) = Formula = "=exp(-" & c.Address & "/$b$3)"
    Next
    Set d = Range("c5", Range("c5").End(xlDown))
    Set i = Range("d5", Range("d5").End(xlDown).Offset(0, 1))
    Range("c3:e3").FormulaArray = "=LINEST(" & d.Address & "," & i.Address & ",TRUE,FALSE)"

    ResponderEliminar
    Respuestas
    1. Hola,
      me faltaría ver la disposición de datos,
      pero veo algunas cosas 'raras', en concreto en los dos bucles que rellenan algunas celdas, creo que ocupadas con los datos desde donde se calcula la regresión, en concreto las variables independientes.
      En lugar de
      Cells(i, 4) = Formula = "= .....
      debería poner
      Cells(i, 4).Formula = "= .....

      Cambia esto y prueba
      Slds

      Eliminar
  36. Tienes razón en tu observación así como tu planteas funciona solo que observo que aún cuando hace bien el cálculo para mis dos variables independientes, cuando me voy a la hoja de cálculo veo que toma como referencia el rango completo en cada celda cuando debería tomar solo la celda de interés e ir corriendo la formula una celda hacia abajo hasta la última celda con datos, es decir, en teoría el cálculo es (1-EXP(-b6/$b$3))*$b$3/b6, para mi primera variable independiente que he llamado F1, pero al correr la macro el selecciona el rango completo de la columna B en donde hay datos (1-EXP(-$B$5:$B$22/$B$3))*$B$3/$B$5:$B$22; aun cuando el resultado que arroja es correcto, quisiera saber si se pude modificar para que solo tome la celda correspondiente y vaya corriendo hacia abajo una a una hasta la última celda con datos, de qué forma quedaría la sintaxis de la macro. La macro completa ha quedado de la siguiente forma:
    Set b = Range("b5", Range("b5").End(xlDown))
    ufila = Range("b" & Rows.Count).End(xlUp).Row
    For i = 5 To ufila
    Cells(i, 4).Formula = "=(1-exp(-" & b.Address & "/$b$3))*$b$3/" & b.Address & ""
    Next
    Set c = Range("b5", Range("b5").End(xlDown))
    ufila = Range("c" & Rows.Count).End(xlUp).Row
    For i = 5 To ufila
    Cells(i, 5).Formula = "=exp(-" & c.Address & "/$b$3)"
    Next

    Set d = Range("c5", Range("c5").End(xlDown))
    Set i = Range("d5", Range("d5").End(xlDown).Offset(0, 1))
    Range("c3:e3").FormulaArray = "=LINEST(" & d.Address & "," & i.Address &

    Muchas gracias por el apoyo.
    Saludos cordiales

    ResponderEliminar
    Respuestas
    1. Hola,
      sería mejor que me enviaras el fichero de trabajo a
      excelforo@gmail.com

      pero primero lee las Normas de uso del blog.
      Slds

      Eliminar
  37. Hola cómo están, quisiera ver si me pueden ayudar tengo la siguiente macro para respaldar en mi maquina un archivo que se encuentra en un servidor.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RespaldarPlantillaGanCapital
    MensajedeRespaldo
    End Sub

    Sub RespaldarPlantillaGanCapital()
    Windows("Plantilla para Reporte de Ganancia de Capital.xls").Activate
    ActiveWorkbook.SaveCopyAs "C:\Documents and Settings\luish\Mis documentos\RESPALDO PLANTILLAS RIESGO DE MERCADO APPS5\BOD\REPORTE GANANCIA DE CAPITAL\Plantilla para Reporte de Ganancia de Capital.xls"
    End Sub

    Sub MensajedeRespaldo()
    Mensaje1 = MsgBox("Respaldo Realizado Exitosamente")
    End Sub

    La macro me funciona bien, hace el respaldo antes de cerrar el archivo, pero me gustaría saber si hay alguna forma de indicarle que me envíe un mensaje donde yo pueda aceptar o cancelar la ejecución de la macro, que si le digo cancelar la macro no se ejecute y cierre el archivo sin respaldarlo, esto con la intención, de que si el archivo se llegase a dañar mientras estoy trabajando con él, no se vaya a guardar un respaldo del archivo dañado.

    Espero me puedan ayudar, gracias de antemano por su atención.

    ResponderEliminar
  38. Hola,
    podrías incluir la pregunta que controle continuar la macro antes de cerrar o no

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    pregunta=Msgbox("continuar", vbyesno)
    if pregunta =vbyes then
    RespaldarPlantillaGanCapital
    MensajedeRespaldo
    end if
    End Sub

    así sólo al responder Si continúas el proceso
    Saludos

    ResponderEliminar
  39. Muchísimas gracias, probé lo que me enviaste y funciona a la perfección, es lo que necesitaba.
    Sin embargo, tengo otra duda que no he podido resolver, cómo puedo hacer para que el respaldo sea guardado con la fecha del día por ejemplo “Plantilla para Reporte de Ganancia de Capital_07102013.xls”, esto con la intencion de tener un control diario, Espero me puedan ayudar, les agradezco su paciencia y ayuda. Saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      me alegro te sirviera.
      Para componer ese texto del archivo podrías generar un String con el texto “Plantilla para Reporte de Ganancia de Capital_" & Date & ".xls”

      Si te fallara por que incluye las barras de separación, emplea la función Replace:
      “Plantilla para Reporte de Ganancia de Capital_" & Replace(Date,"/","") & ".xls”
      creo te debería funcionar.
      Slds

      Eliminar
  40. Muchisimas gracias me funciono bien de la segunda forma que me recomiendas, era lo que buscaba, de nuevo gracias por el apoyo. Saludos cordiales

    ResponderEliminar
  41. Hola buenas tardes, de antemano muchas felicidades y gracias por tener esta página que nos ayuda muchisimo a personas que nos gusta Excel.Quisiera pedir su gran ayuda ya que tengo en las celdas A10 = 5 , C10 = 10, E10 = -8 , G10 = -2 , I10 = 3.
    Y quisiera una fórmula para que en la celda K10 muestre la suma de los valores POSITIVOS y en la L10 la suma de los negativos. Cabe mencionar que las celdas estan en columnas no continuas y los valores que se escribibirán en las celdas , se irán modificando y lo importante es que dependiendo de si es positivo o negativo, lo sume y muestre automáticamente ya sea en la celda K10 o L10. Muchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola!,
      bueno la fórmula par los positivos (en K10) sería:
      =SUMAR.SI($A$10:$J$10;">0")
      y para los negativos en L10
      =SUMAR.SI($A$10:$J$10;"<0")

      Estoy suponiendo que en las celdas intermedias no hya valores numéricos, pudiendo ser de texto.

      Saludos

      Eliminar
  42. Hola de nuevo yo, gracias por la fórmula, perdón pero omití decir que en la celdas intermedias hay fechas, si las pongo con formato de texto, NO las considera en el cálculo tal cual me envía la fórmula y me da el resltado correcto.
    Voy a utilizar esta opción, pero tambien quiero preguntarle si para efecto de hacer fórmulas con las fechas como por ejemplo un reporte de una fecha específica, no genera un error por sex de formato texto.

    ResponderEliminar
    Respuestas
    1. Hola,
      es importante exponer el caso lo más concreto posible, ya que la solución puede variar.

      Si has convertido las fechas en texto ya no son operables, y por tanto generarían algú tipo de error al resumir la información.

      Es una forma peculiar y no muy óptima intercalar campos de fechas con valores, ya que a la hora de resumir información, nos encontramos con este tipo de problema.

      Tendría que ver el planteamiento general de tus datos para dar una opinión y buscar una solución.
      Si quieres envíame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
  43. Buenas tardes, tengo la siguiente macro:
    Sub prueba()
    Sheets("Hoja1").Select
    Range("b65536:d65536").Select
    Selection.End(xlUp).Select
    Selection.Copy
    Range("g3:g5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

    End Sub
    Mi intención es copiar la última fila con valor en el rango de b a d y luego pegarlo como valor y traspuesto en el rango g4:g6, este rango va a ser fijo siempre, lo que se va a actualizar son los datos que se pondrán en las columnas b a d. Al correr la macro solo copia la última fila con valor pero de la columna b solamente, no copia el rango completo de b a d. Espero me puedan ayudar con la sintaxis, ya que no se qué es lo que pueda estar mal en la macro, ya he probado otras sintaxis pero siempre copia solamente un valor, el que aparece en la última fila de la columna b y lo que quiero es que me tome todo el rango (b, c y d) y luego lo pegue traspuesto y como valor en el rango g4:g6.

    Saludos y gracias de antemano por su ayuda.

    ResponderEliminar
    Respuestas
    1. Hola!,
      es mejor definas una variable para construir el rango a copiar, algo así debería funcionar:
      Sub prueba()
      fila = Sheets("Hoja1").Range("B" & Rows.Count).End(xlUp).Row
      Range("B" & fila & ":D" & fila).Copy
      Range("g3:g5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=True
      End Sub

      Slds

      Eliminar
  44. Muchas gracias funciona perfecto de esa forma, saludos.

    ResponderEliminar
  45. que tal, estoy aprendiendo a programar en VBA y estoy tratando de hacer lo siguiente, espero alguien me pueda ayudar, gracias por su tiempo.

    Sub formula()

    Sheets("Datos").Select
    Range("e2").formula = "=SI(T7="","",SI($AG$6="x",0,SI(A7="",0,Datos!$B$10)))"

    End Sub

    señala la x entre comillas y me dice esperaba la finalizacion de la instruccion.

    asumo que es por que piensa que ya cerre comillas para el texto.

    agradeceria su ayuda con este error.


    ResponderEliminar
    Respuestas
    1. Hola Chelo,
      si vas a emplear .Formula deberís poner los nombres de las funciones en inglés (en tu caso IF), si quieres usarlas en español usa. FormulaLocal.

      Tu macro quedaría entonces
      Sub formula()
      Sheets("Datos").Range("e2").FormulaLocal = "=SI(T7="""";"""";SI($AG$6=""x"";0;SI(A7="""";0;Datos!$B$10)))"
      End Sub

      Como ves todas las comillas 'internas' van con dobles comillas.
      Saludos

      Eliminar
    2. como estas, muchas gracias por responder.

      mira intente hacer como expusiste arriba ahora se arreglo el error de sintaxis pero apareacio otro al ejecutar el marcro.

      dice "error 1004 en tiempo de ejecución
      error definido por la aplicación o el objeto"

      te explico el contexto por que posiblemente sea eso.
      tengo varias hojas de calculo, y la formula que intento insertar es una formula que tiene datos de una hoja con celdas de otra hoja tambien por eso eso dice datosb10.

      muchas gracias por la ayuda.

      Eliminar
    3. muchas gracias ya se arreglo era una cuestion de poner comas y no punto y coma.

      fuiste de mucha ayuda, espero poder participar activamenete en el foro en el futuro.

      Saludos,
      Chelo.

      Eliminar
    4. Perfecto Chelo!
      las comas o el punto y coma depende de la configuración de cada sistema...
      Un cordial saludo

      Eliminar
  46. Gracias por la oportunidad

    Tengo un libro de excel que exporto desde una aplicación web, pero cada vez que lo descargo la cantidad de fila es diferente. Creo unas macro para una columna especifica llamada peso (esta contiene un valor en kilos) inserto una columna nueva y hago el calculo de pasar kilos a libras y funciona bien por copio toda la formula manual. pero cuando lo hago con una macro y cambio a otro libro que tenga mas filas. solo me realiza la operacion hasta el rango en que grabe la macro. como podria cambiar la macro para que me tome todo el rango sin importar el numero de filas que tenga el libro

    Gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola William,
      en tu macro deberás contruir el rango como dinámico; hay varias formas:
      Una empleando la propiedad .CurrentRegion
      así por ejemplo en lugar de Range("A1:D200").select
      es mejor emplear
      Range("A1").CurrentRegion.Select

      así no importará dónde acabe el rango de trabajo, es decir, el número de filas que tenga.

      Existen algunas más, pero este es el más sencillo.

      Espero te haya orientado.
      Slds

      Eliminar
    2. Gracias por tu respuesta, pero no logro que funcione, le podría enviar el archivo de excel y las indicaciones de los que quiero hacer. me podría indicar su cuenta de correo electrónico. No sabe cuanto le agradezco

      Eliminar
    3. a continuación pego el código de la macro, este me funciona para un libro con 73 filas, pero si lo hago en otro con mas filas, me deja las celdas sin calcular y si el libro tiene menos filas entonces las llena de ceros.

      Tengo en la columna D el peso en Kilos, inserto una columna E en donde aplico la formula ( D2/2.204) y despues la coipo manualmente por toda la columna hasta terminar de llenar los valores, Lo que deseo hacer es que no importando el numero de filas del libro me ejecute la formula en toda la columna insertada basandose en la cantidad de celdas llenas de la columna anterior

      Sub Macro4()
      '
      ' Macro4 Macro
      '
      ' Acceso directo: CTRL+l
      '
      Columns("E:E").Select
      Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      Columns("E:F").Select
      Selection.NumberFormat = "0.00"
      Selection.NumberFormat = "0.0"
      Selection.NumberFormat = "0"
      Range("E2").Select
      ActiveCell.FormulaR1C1 = "=RC[-1]/2.204"
      Range("E2").Select
      Selection.AutoFill Destination:=Range("E2:E3"), Type:=xlFillDefault
      Range("E2:E3").Select
      Range("E3").Select
      Selection.AutoFill Destination:=Range("E3:E72"), Type:=xlFillDefault
      Range("E3:E72").Select
      ActiveWindow.SmallScroll Down:=-99
      Range("E2").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      ActiveWindow.SmallScroll Down:=-54
      Range("F2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Application.CutCopyMode = False
      Columns("D:E").Select
      Selection.Delete Shift:=xlToLeft
      Range("D1").Select
      ActiveCell.FormulaR1C1 = "Peso"
      Range("D2").Select
      ActiveWindow.SmallScroll Down:=-15
      End Sub

      Eliminar
    4. Hola Willian,
      mejor envíame el ejemplo a
      excelforo@gmail.com
      con los pasos que quieres que realice la macro...
      Lo que adjuntas en el comentario, al haberse grabado con el asistente ha generado demasiada 'basura'
      ;-)
      Slds

      Eliminar
    5. ;-)
      dejo la solución aquí
      Sub Macro1()
      Dim fila As Long
      Columns("E:E").Insert Shift:=xlToRight
      fila = Range("A1").CurrentRegion.Rows.Count
      With Range("E2")
      .FormulaR1C1 = "=RC[-1]/2.204"
      .AutoFill Destination:=Range("E2:E" & fila)
      End With
      End Sub

      Slds cordiales

      Eliminar
  47. Hola buenas tardes, por favor si me pueden ayudar con una duda que tengo y es la siguiente: Por medio de una macro y utilizando variables quiero utilizar el autofiltro de una columna pero que cumpla 2 condiciones, es una lista de boletos en orden.
    Por ejemplo que me muestre los boletos del 3 al 9.... Estoy utilizando un userform donde capturo el boleto inicial y final, filtra pero no me muestra nada. Talvez sea porque en los criterios uso el "mayor o igual" y "menor o igual".

    inicial = Val(TextBox1)
    final = Val(TextBox2)
    ActiveSheet.Range("$A$8:$U$713").AutoFilter Field:=2, Criteria1:=">=inicial", _
    Operator:=xlAnd, Criteria2:="<=final"

    ResponderEliminar
    Respuestas
    1. Hola, mejor prueba de esta manera:
      Dim inicial As String, final As String
      final = "<=" & Val(TextBox1)
      inicial = ">=" & Val(TextBox2)

      ActiveSheet.Range("$A$8:$U$713").AutoFilter Field:=2, Criteria1:=inicial, Operator:=xlAnd, Criteria2:=final

      Debería funcionarte sin problemas...

      Un saludo

      Eliminar
  48. que tal, he estado praticando y aprediendo , tengo una duda.

    tengo un macro que hace lo siguiente, borra e inicializa una hoja de calculo que elabore, pero el probelma es que se demora demasiado, lo mismo ocn un formulario que introduce datos en unas celdas definidas, funciona y hace todo lo que debe hacer, pero le problema es que se demora mucho , por ejemplo el cuestionario deberia ser bastante rapido pero para escribir tres o cuatro datos se demora casi y segundo, no se si esoty haiendo alguna cosa que no debería o extra que hace que se haga tan lento.

    a continuacion pongo los dos.



    Private Sub CommandButton2_Click()
    Dim datosa1 As String 'Nombre del Poryecto'
    Dim datosa2 As String 'Ubicacion'
    Dim datosa3 As String 'Obra'
    Dim datosa4 As String 'Numero de Tramite'
    Dim datosa5 As String 'Responsable'
    Dim datosa6 As String 'Oferente'

    datosa1 = txta1
    datosa2 = txta2
    datosa3 = txta3
    datosa4 = txta4
    datosa5 = txta5
    datosa6 = txta6

    Sheets("Datos").Activate
    Sheets("Presupuesto").Range("c5").Value = datosa1
    Sheets("Presupuesto").Range("c6").Value = datosa2
    Sheets("Presupuesto").Range("c7").Value = datosa3
    Sheets("Presupuesto").Range("f7").Value = datosa4
    Sheets("Presupuesto").Range("c8").Value = datosa5
    Sheets("Presupuesto").Range("f5").Value = datosa6


    Me.Hide

    End Sub

    Private Sub CommandButton3_Click()
    Me.Hide
    End Sub

    Private Sub CommandButton4_Click()


    Sheets("Datos").Range("b15").Value = CDbl(txtb2.Text)
    Sheets("Datos").Range("b16").Value = CDbl(txtb3.Text)

    Sheets("Datos").Range("b12").Value = CDbl(txtb4.Text)
    Sheets("Datos").Range("b13").Value = CDbl(txtb5.Text)
    Sheets("Datos").Range("b11").Value = CDbl(txtb6.Text)

    Sheets("Datos").Range("b17").Value = CDbl(txtb7.Text)
    Sheets("Datos").Range("b18").Value = CDbl(txtb8.Text)

    Sheets("Datos").Range("b20").Value = CDbl(txtb9.Text)

    Me.Hide

    End Sub

    Private Sub CommandButton5_Click()
    Me.Hide
    End Sub

    Private Sub CommandButton7_Click()
    Me.Hide
    End Sub

    Private Sub CommandButton9_Click()
    Me.Hide
    End Sub



    ResponderEliminar
  49. es un poco mas largo pero este si se demora mucho, a veces es incuslo mas largo que borrar e introducir los datos manualmente.


    saludos y gracias por al ayuda.

    Marcelo.

    ResponderEliminar
    Respuestas
    1. Hola,
      me parece que poco pedes hacer, ya que tomas los datos de tu UserForm, por lo que parece tendrás que 'aguantar' el proceso.
      De todas formas te paso este link con recomendaciones a la hora de acelerar el proceso:
      http://excelforo.blogspot.com.es/2012/12/vba-maneras-de-acelerar-nuestras-macros.html
      hay alguna de ellas que puedes aplicar... y que quizá acelere tu macro.
      Saludos cordiales

      Eliminar
  50. Hola quisiese que me ayudasen para crear una la manera mas practica de hacer lo siguiente, en una columna debo colocar una formula que me discrimine con una letra (que esta en otra columna) si corresponde a una formula u a otra, el problema que son aprox. 60.000 registros y quedaría muy pesada con la formula lógica =IF (actualmente esta es la formula (=IF($M10=$V$5,$N10*$N$2+$O10*$O$2+$P10*$P$2+$Q10*$Q$2+$R10*$R$2,IF($M10=$V$6,$N10*$N$3+$O10*$O$3+$P10*$P$3+$R10*$R$3,0)), ademas en la planilla existen otras formulas, quiero ver la factibilidad de tener una macro.
    PD. esto quiero hacerlo ya que esta planilla va a terreno para reportar los avances que se generan en el la obra y así realizar los avances semanales.
    agradeciendo.

    ResponderEliminar
    Respuestas
    1. Hola!!
      bueno, todo dependerá del número de condicionantes a añadir, en el ejemplo que muestras son dos y no parece muy retorcido aplicar un doble SI anidado, podría mejorarse un poco:
      =IF(($M10=$V$5;SUMPRODUCT(N10:R10;N2:R2);IF(($M10=$V$5;SUMPRODUCT(N10:R10;N3:R3;0))

      Una macro no creo mejorara en exceso el cálculo, ya que la operación se deberá hacer igualmente... quizá para ese número de registros, si en tu equipo se ralentiza mucho empleando Excel, sea mejor realizar todos los cálculos con Access.

      Saludos cordiales

      Eliminar
  51. Buenas me gustaria saber si me pueden ayudar ya que no se si estoy haciendo bien la sintaxis, tengo la siguiente macro con la que intento simular la funcion cortar de excel. Lo que busco es que la macro corte todas aquellas filas que tengan la palabra "Cortar" en la columna especificada y que luego la pegue en la hoja "RRHH" pero al correr la macro solo me pega una sola fila. La idea es que esas filas no queden en la hoja "D_PROVISIONADA" por eso uso la opción cortar pero no logro que me pegue todas las filas.

    Espero me puedan ayudar, Muchas gracias de antemano.
    Sub prueba()
    Windows("PLANTILLA D_PROVISIONADA.xls").Activate
    Sheets("D_PROVISIONADA").Select
    fila = Range("a165000").End(xlUp).Row
    For i = fila To 1 Step -1
    If Cells(i, 24) = "Cortar" Then
    Rows(i).Cut
    Sheets("RRHH").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema creo reside más en la manera de Cortar y Pegar, prueba con esta macro:
      Sub prueba()
      'Windows("PLANTILLA D_PROVISIONADA.xls").Activate
      Set sh = Sheets("D_PROVISIONADA")
      fila = sh.Range("a165000").End(xlUp).Row
      For i = fila To 1 Step -1
      If sh.Cells(i, 24) = "Cortar" Then
      sh.Rows(i).Cut Destination:=Sheets("RRHH").Range("A165000").End(xlUp).Offset(1, 0)
      Application.CutCopyMode = False
      End If
      Next i
      End Sub


      Saludos

      Eliminar
  52. Muchas gracias por tu ayuda, probe con tu mejora y funciona a la perfección, gracias nuevamente, saludos cordiales.

    ResponderEliminar
  53. Buenas tardes, me gustaría saber si me pueden ayudar con la siguiente macro, todos los días recibo un archivo con los datos de unos ACTIVOS que son colocados en diferentes hojas dependiendo del activo (ACTIVO1, ACTIVO2,…ACTIVOn), es decir, hay una hoja para cada activo. Hice una macro en el archivo "DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls" con la intención de extraer solo el rango que me interesa el cual es fijo para cada activo ("B4:B23"). La macro me funciona bien pero tengo el problema que no siempre hay información para algún activo en particular, y yo hice la macro considerándolos todos. Por ejemplo, si el día de mañana no hay información para el ACTIVO3 esa hoja no va a estar en el archivo que recibo, por lo que, me genera un error de depuración y se detiene sin completar la rutina, no me pega nada.
    Mi pregunta es: existe alguna forma de decir en la macro, que si una hoja determinada no está continúe el proceso, que no se detenga y pegue el resto de las hojas que si tienen información, es decir que completa la rutina con los activos que si aparecen en el archivo y que no se detenga porque falta alguno en particular.
    Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
    Sheets("ACTIVO1").Select
    Range("B4:B23").Select
    Selection.Copy
    Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
    Sheets("ACTIVO2").Select
    Range("B4:B23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
    Range("C2").Select
    ActiveSheet.Paste
    Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
    Sheets("ACTIVO3").Select
    Range("B4:B23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
    Range("D2").Select
    ActiveSheet.Paste
    Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
    ActiveWindow.ScrollWorkbookTabs Sheets:=-3
    Sheets("ACTIVO4").Select
    Range("B4:B23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
    Range("E2").Select
    ActiveSheet.Paste

    Agradeciéndoles de antemano su ayuda quedo a la espera de su siempre oportuna respuesta,

    Saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Hola que tal!
      bueno, para saltar fallos de ese tipo, podrías incluir unas líneas de depuración de error:
      On error Resume Next
      '... el código
      On Error GoTo 0


      Puedes incluirlo al principio y final de tu código o grupo a grupo de instrucciones...
      Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
      Range("C2").Select
      ActiveSheet.Paste
      Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
      Sheets("ACTIVOx").Select
      Range("B4:B23").Select
      Application.CutCopyMode = False
      Selection.Copy

      Espero te funcione
      Un saludo

      Eliminar
  54. Buenos días, gracias por responder, tu consejo lo he puesto en práctica, si bien es cierto no detiene la rutina, no me genera el error de depuración, me está pegando dos veces la información del activo que esta antes del activo que no aparece en el archivo, creo es un error de mi parte al explicarme. El archivo que recibo como dije hay una hoja para cada activo, yo lo que hago es llevarme el mismo rango de cada hoja ("B4:B23") para una sola hoja en el archivo donde está la macro ("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls") donde cada columna comenzando en la columna B2 de ese archivo va a ser cada uno de los activos. Como te dije, si del archivo que recibo hoy no está el ACTIVO3 me generaba un error y se paraba la rutina, con tu recomendación no se para, pero me está pegando los datos el ACTIVO2 (el que esta antes), lo pega dos veces en la columna del ACTIVO2 y en el del ACTIVO3 que no tuvo información para ese día. La idea es que no se detenga, pero que a su vez esa columna quede en blanco y continúe pegando el ACTIVO4 (si tiene información sino el que siga que tenga información) en su correspondiente columna. Como te comente, así como puede faltar el ACTIVO3 puede faltar cualquier otro activo de los que tengo definidos en las columnas, la intención es que no se detenga y pegue los datos de los activos que si están pero sin duplicar información y pegándolos en el orden que les he dado en las columnas.
    Espero con esto aclarar un poco lo que espero haga la rutina, agradeciéndote tu apoyo.
    Saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Ummm
      por lo que entendí viendo tu código es que tienes un libro origen:
      SALIDAS SIMULACIONES DEL VaR.xls
      con cuatro posibles hojas ACTIVO1, ACTIVO2, ACTIVO3 y ACTIVO4 pudiendo faltar una o varias de ellas.
      En tu código pasas una por una por esas hojas copiando la columna B3:B24 de cada hoja para pegarla en otro libro destino
      DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls
      donde vas pegando de manera continua en las celdas
      B2, C2, D2 y E2

      correcto?
      si faltara una de las hojas ACTIVOx se detendría el proceso... por eso te indicaba que al principio y fin de cada grupo de copiado y pegado añadieras el depurador
      On error Resume next
      ...
      On error Goto 0

      con eso consigues que al No detectar una de las hojas ACTIVOx saltara a la siguiente instrucción...

      Otra posibilidad sería añadir nombres de línea al principio de cada 'grupo' de copiado, con un depurador On error goto Linea

      por ejemplo:
      On erro goto ACTIVO2
      Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
      Sheets("ACTIVO1").Select
      Range("B4:B23").Select
      Selection.Copy
      Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
      Range("B2").Select
      ActiveSheet.Paste

      ACTIVO2:
      On error goto ACTIVO3
      Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
      Sheets("ACTIVO2").Select
      Range("B4:B23").Select
      Application.CutCopyMode = False
      Selection.Copy
      Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
      Range("C2").Select
      ActiveSheet.Paste

      ACTIVO3:
      On error goto ACTIVO4
      Windows("SALIDAS SIMULACIONES DEL VaR.xls").Activate
      Sheets("ACTIVO3").Select
      Range("B4:B23").Select
      Application.CutCopyMode = False
      Selection.Copy
      Windows("DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls").Activate
      Range("D2").Select
      ActiveSheet.Paste

      Etc, etc...


      De manera similar, podrías aplicar un condicional que verifique si existe la hoja ACTIVOx en cuestión, y en caso contrario lanzar el salto a la línea siguiente

      Podrías crear una función VBA:
      Function ExisteHoja(NombreHoja As String) As Boolean
      ExisteHoja = False
      For Each hojas In Worksheets
      If NombreHoja = hojas.Name Then
      ExisteHoja = True
      Exit Function
      End If
      Next hojas
      End Function

      que completando el nombre de la hoja te devuelva TRUE o FALSE, existe la hoja o no, y aplicarlo al condicional de control
      IF ExisteHoja("ACTIVO1)=False then goto ACTIVO2
      ....

      Saludos

      Eliminar
  55. Hola nuevamente, hice lo que me recomendaste le agregue el nombre al principio de cada grupo On erro goto ACTIVO2 pero en este caso me vuelve a generar el error se detiene en el activo que falta, en cuanto a tu segunda recomendación te debo confesar que no se cómo aplicarla, es decir, la función VBA:
    Function ExisteHoja(NombreHoja As String) As Boolean
    ExisteHoja = False
    For Each hojas In Worksheets
    If NombreHoja = hojas.Name Then
    ExisteHoja = True
    Exit Function
    End If
    Next hojas
    End Function

    Dónde debo ponerla en cuál de los archivos en SALIDAS SIMULACIONES DEL VaR.xls o en donde tengo mi código DATOS DEL SIAR PARA ESTIMACION DEL C-VaR y EVT.xls, si es en este ultimo dónde debo poner esta sentencia al principio de mi código al final. Por otro lado, la condicional de control
    IF ExisteHoja("ACTIVO1)=False then goto ACTIVO2 Dónde la debo poner y cuantas veces debo hacerla, tengo que hacerla para cada activo que tengo, debo ponerla al principio de cada grupo, al final, me gustaría me ayudaras ya que no se cómo es el orden de esto que me propones con el código que yo ya tengo, como los uno los dos.
    Gracias de antemano por tu ayuda y disculpa las molestias.

    ResponderEliminar
    Respuestas
    1. Si quieres una solución personalizada, envíame un correo con el fichero a
      excelforo@gmail.com

      Un saludo

      Eliminar
  56. Hola!
    Junto con saludar, me gustaría saber si me puedes ayudar con el siguiente problema, estoy haciendo una macro que finalice al agregar una función SI en la que debe indicar si la suma de dos rangos distintos son iguales, y en caso contrario indicar cuál es el mayor. El asunto es que los rangos son variables y dependen de cada ejecución.
    Grabando la creación de la fórmula obtuve lo siguiente:

    ActiveCell.FormulaR1C1 = _
    "=IF(SUM(RC[-3]:RC[-1])SUM(R[-2]C:R[-1]C),""Exceso Demanda"",""Balanceado""))"

    Lo primero que hice fue modificar el primer 3 por una variable j (que contiene el número que quiero) a través de la concatenación con la instrucción, pero me genera diversos errores; leí más arriba que debería haber triples comillas, pero me sigue generando error. Si uso la j directamente el error es de tipo, por lo que la agregué a través de CStr, pero ahora me aparece un "error definido por la aplicación".
    Esto es lo último que intenté:

    d1 = CStr(j)
    ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-""" + d1 + """]:RC[-1])SUM(R[-2]C:R[-1]C),""Exceso Demanda"",""Balanceado""))"

    Si me pudieras ayudar sería genial.
    De antemano gracias.

    ResponderEliminar
    Respuestas
    1. Hola Felipe,
      a primera vista parece un problema de la estructura de la fórmula.
      Yo probaría con:
      d1 = CStr(j)
      ActiveCell.FormulaR1C1 = _
      "=IF(SUM(RC[-" & d1 & "]:RC[-1])<>SUM(R[-2]C:R[-1]C),""Exceso Demanda"",""Balanceado"")"

      parece te sobra un paréntesis al final.. y luego, también es mejor emplees & en lugar de + para concatenar.

      Saludos

      Eliminar
    2. Hola Ismael,
      Muchas gracias por la ayuda, funcionó; de hecho pensé que había probado esa combinación, pero supongo que entre tanto cambio se me pasó por alto.
      El paréntesis extra es debido a que hay un segundo IF dentro de la fórmula, cuando se publicó el comentario se perdieron algunos elementos. Pero lo importante es que me funcionó. Muchas gracias.
      Saludos

      Eliminar
  57. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  58. Hola... he generado una tabla en la que establecí subtotales (debe y haber)... lo que necesito es establecer el saldo entre ellas, es decir, que muestre la diferencia sólo en la fila de subtotales...
    Ojalá me puedas ayudar... sería de mucha utilidad... gracias de antemano

    ResponderEliminar
  59. Hola... quiero darte las gracias por haber dado respuesta pronta a mi inquietud sobre los subtotales.
    Ahora te molesto con esto: en cierta parte de código vba he encontrado esta sentencia:
    "=SUMIF(R5C1:R[-1]C1,R[-1]C1,R5C10:R[-1]C10)"
    y no logro entender a qué se refiere lo que está escrito entre los corchetes... podría ayudarme si es posible?
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola, ;-)
      Eses forma de escribir en las macros es la forma relativa en estilo F1C1
      (ver http://excelforo.blogspot.com.es/2010/06/celdas-con-referencia-de-estilo-f1c1.html).

      Cada celda o rango esta determinado por la posición de la celda activa, por ejemplo
      R5C1 indica 5 filas hacia abajo y una columna a la derecha desde la celda activa
      R[-1]C1 indica 1 filas hacia arriba y una columna a la derecha desde la celda activa

      Todo ello compone finalmente rangos dentro de la función SUMAR.SI

      Un saludo

      Eliminar
  60. Elaboré una macro similar para el traslado en un mismo libro y me funcionó correctamente... hice los arreglos que creí necesarios para que el traslado pudiera efectuarse de un libro a otro y no me funciona... podrías revisarla y decirme en dónde está el error? creo que lo que pasa es que no se identifica el archivo origen correctamente... gracias de antemano
    Sub TrasladoResumenCheques()

    'rutina que graba el RESUMEN DE CHEQUES MENSUAL a la Plantilla Contable

    Workbooks.Open "C:\Users\Edvin\Documents\Uno\PlantillaContableSanatorioNuevaAgosto14.xlsm"
    Sheets("DIARIOMAYOR").Activate
    'Windows("Voucher.xlsm").Activate
    'Sheets("RESUMEN").Select

    'se busca la primer fila libre en DIARIOMAYOR
    filalibre = Sheets("DIARIOMAYOR").Range("A1048576").End(xlUp).Row + 1

    Windows("Voucher.xlsm").Activate
    Sheets("RESUMEN").Select
    'se trasladan los datos
    'Sheets("voucher").Select
    ActiveSheet.Range("A2").Select
    Fila = 2

    While ActiveCell.Value <> ""

    'traslado de los datos necesarios

    Sheets("DIARIOMAYOR").Cells(filalibre, 1) = ActiveCell.Offset(0, 0) 'FECHA
    Sheets("DIARIOMAYOR").Cells(filalibre, 3) = ActiveCell.Offset(0, 1) 'NO. CHEQUE
    Sheets("DIARIOMAYOR").Cells(filalibre, 5) = ActiveCell.Offset(0, 4) 'CODIGO
    Sheets("DIARIOMAYOR").Cells(filalibre, 6) = ActiveCell.Offset(0, 5) 'CUENTA
    Sheets("DIARIOMAYOR").Cells(filalibre, 8) = ActiveCell.Offset(0, 6) 'DEBE
    Sheets("DIARIOMAYOR").Cells(filalibre, 9) = ActiveCell.Offset(0, 7) 'HABER
    Sheets("DIARIOMAYOR").Cells(filalibre, 7) = ActiveCell.Offset(0, 8) 'CONCEPTO

    'incremento la variable fila para repetir el bucle
    filalibre = filalibre + 1

    'repito el bucle para los items siguientes
    ActiveCell.Offset(1, 0).Select
    Wend
    'se informa que los datos se trasladaron correctamente
    MsgBox "DATOS GUARDADOS CORRECTAMENTE"
    Sheets("DIARIOMAYOR").Activate

    ResponderEliminar
    Respuestas
    1. Hola!
      mejor envíame los ficheros a
      excelforo@gmail.com

      los revisaré y te comentaré.

      Saludos

      Eliminar
  61. Hola Ismael:
    He enviado los ficheros como sugieres, para tu revisión y comentarios.
    Saludos

    ResponderEliminar
    Respuestas
    1. Perfecto!
      ya los revisé y contesté.
      Saludos

      Eliminar
    2. La macro funcionó tal cual esperaba... gracias por tu apoyo...
      ¡Que estés bien siempre! ... Saludos

      Eliminar
  62. Buenas tardes, tengo la siguiente macro que tiene por objetivo copiar el valor que este en la columna 13 siempre y cuando el valor de la celda que aparezca en la Columna 10 sea igual a cero y me lo pegue en la misma fila pero en la columna 16 como valores sustituyendo en que allí está. He logrado que lo haga, pero me está repitiendo el mismo valor filas hacia abajo hasta que encuentra otra celda igual a cero en la columna 10 y vuelve a repetir ese dato. Lo que quiero es que no repita ese valor, que deje el que está en esa fila y solo copie el valor de la fila que es igual a cero en la columna 10. Espero me puedas ayudar.
    Saludos cordiales.
    fila = 4
    Do While (Cells(fila, 1) <> "")
    If (Cells(fila, 10) = 0) Then Cells(fila, 13).Select
    Selection.Copy

    Cells(fila, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    fila = fila + 1

    Loop

    ResponderEliminar
    Respuestas
    1. Hola Sadeliano,
      tendrías que cerrar el bloque IF
      fila = 4
      Do While (Cells(fila, 1) <> "")
      If (Cells(fila, 10) = 0) Then
      Cells(fila, 13).Select
      Selection.Copy
      Cells(fila, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
      end if
      fila = fila + 1
      Loop

      es decir, que solo cuando sea cero copie y pegue, en el resto de caso no haga nada.
      Saludos

      Eliminar
  63. Hola Ismael, otra vez por acá para preguntarte lo siguiente:
    tengo un formulario con el siguiente texbox:

    Private Sub CommandButton1_Click()
    With Me.TextBox1
    .Value = Range("B1").Value
    .Value = Format(.Value, "$#,##0.00")
    End With

    la cuestión es que quiero colocar en el Texbox en vez del valor de la celda B1 (.Value = Range("B1").Value), una formula, lo intentado de varias formas pero al ejecutarlo en el texbox sale falso, por ejemplo lo he hecho con .FormulaLocal = "=SUMA(C1:C2)" y .Value = "=SUMA(C1:C2)", pero no me funciona, en vez del valor de la formula sale falso en el texto. Gracias por tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola,
      un TextBox es eso precisamente.. una Caja de Texto, y no admite fórmulas...
      tendrías que realizar la suma 'por fuera' con alguna variable y luego agregar el resultado al TextBox.

      Saludos!

      Eliminar
  64. Gracias Ismael, ya resolví el problema, lo solucione con Application.Evaluate
    saludos.

    ResponderEliminar
  65. tengo dos formulas, A y B, y una variable (x) si x>0 aplicar formula A, si x=0 aplicar formula B. ¿pueden ayudarme?

    ResponderEliminar
    Respuestas
    1. Hola Fer,
      pues la idea es tal cual la cuentas:
      =SI(x>0;formula A; formulaB)
      un ejemplo
      =SI(A1>0;B1*C1;B1/C1)

      Saludos

      Eliminar
    2. e intentado varias maneras y una de estas es; =SI(O(variable>0;variable=0);(funcion 1);(funcion 2), primero use Y, pero no podia introducir una funcion, luego use O y me permitio usar funcion y quedo mas o menos asi;
      =SI(O(O22>0;O22=0);(Z22-(1-Z22)/(T22*TAN(RADIANES(O22))));(1-(2*X22/(5,1416)))), espero que este bien muchas gracias!

      Eliminar
    3. Si lo que debes aplicar es el criterio O, parece más sencillo:
      =SI(O22>=0;....)
      Saludos

      Eliminar
  66. Hola, soy nueva con las macros, necesito aplicar una formula en una columna con 500 filas, se trata de buscar un valor y colocar la fecha en la que esta propuesto dicho valor, en cada fila cambia el valor y por ende la fecha, no se cómo podría hacerla con una macro, espero me puedan ayudar?? la formula que utilizo es la de "BUSCAR", Gracias :)

    ResponderEliminar
    Respuestas
    1. Hola Esperanza,
      seguramente emplear la función BUSCARV sea mucho más eficiente que BUSCAR y emplear una macro... o en todo caso, dependiendo de la estructura de campos donde haya que buscar, una combinación de INDICE y COINCIDIR

      Salvo que hay que ir reemplazando en esas 500 celdas el valor por la fecha encontrada no pienso que una macro te aporte nada...

      Un saludo

      Eliminar
    2. Gracias por responder, intentare la formula buscarv :)

      Eliminar
  67. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  68. Meses Enero Febrero Marzo Abril Mayo Junio 1. Seleccione el rango A3:G6
    Lima 25 15 12 52 23 12 2. Ficha Fórmulas - Grupo Nombres definidos
    Ica 12 16 25 41 36 15 3. Haga clic en Crear desde la Selección
    Trujillo 32 21 32 34 41 31 4. Marque las casillas Fila Superior y Columna izquierda

    5. Al rango B4:D6, asígnele el nombre: Verano
    ASIGNAR NOMBRES A LOS RANGOS 6. Al rango E4:G6, asígnele el nombre: Otoño

    RESOLVER: Fórmulas Respuestas En las Fórmulas, utilice los nombres de rangos

    Ventas de Enero y marzo 138 OPERADORES DE REFERENCIA

    Ventas desde Enero hasta marzo 190 : Rango
    ; Unión
    Ventas en Febrero en Ica 16 Espacio Intersección

    Ventas de Enero en Ica y Abril en Trujillo 46

    Ventas de Ica en Otoño 92

    Las Ventas de Lima en el verano y 158
    Trujillo en el otoño

    ResponderEliminar
    Respuestas
    1. Hola Luisa,
      que tal?, espero te encuentres bien igualmente.

      No entiendo qué necesitas ni cuál es la duda que planteas..????
      Si expones con algo más de claridad la duda, quizá pueda ayudarte.

      Un saludo

      Eliminar
  69. Hola Expertos,

    Estoy dando mis primeros pasos con macros y me he atascado en esta formula pues me da error. ¿Podéis ayudarme a identificar el error?

    Sub diaslaborables()
    For t = 2 To 8000
    If Cells(t, 6) <> "" And Cells(t, 13) <> "" Then
    Cells(t, 22).Formula = "=DIAS.LAB.INTL(L[" & t & "];M[" & t & "])"
    Else
    End If
    Next
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      prueba quitando los corchetes de la función:

      Sub diaslaborables()
      For t = 2 To 8000
      If Cells(t, 6) <> "" And Cells(t, 13) <> "" Then
      Cells(t, 22).Formulalocal = "=DIAS.LAB.INTL(L" & t & ";M" & t & ")"
      Else
      End If
      Next
      End Sub

      saludos

      Eliminar
    2. Muchísimas gracias Ismael!

      Eliminar
  70. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  71. Hola amigos, les estaría muy agradecido de poderme ayudar, les cuento;
    necesito introducir un On Error Resume Next antes o después del siguiente código
    '
    aquí se encuentra una instrucción previa, la cual se ejecuta de forma extraordinaria, pero en la siguiente no lo hace, ya que la URL presenta problemas para poder extraer la información en este caso la "10002" y así sucesivamente, tengo como 100 URL´s que necesito utilizar y no dudo que como esta hayan otras que puedan presentar el mismo error.

    el apoyo que solicito es como introducir un On Error Resume Next ya que la siguiente extracción de la URL "10006"puede generar o no error.

    el error que me genera un error 1004 y solicita finalizar o depurar

    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://rncenlinea.snc.gob.ve/planilla/index/10002?anafinan=N&anafinanpub=Y&login=N&mostrar=INF" _
    , Destination:=Range("Hoja1!$A$1"))
    .Name = "DatosExternos_8"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "4,7"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With
    Sheets("Hoja1").Select
    Range("C2:C10,C21:C29,C31:C36").Select
    Range("C31").Activate
    Selection.Copy
    Sheets("Datos").Select
    Application.Goto Reference:="R700000C1"
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Application.Goto Reference:="R1C1"
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Datos").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Datos").AutoFilter.Sort.SortFields.Add Key:=Range( _
    "A1:A700000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

    ResponderEliminar
    Respuestas
    1. Hola Chely,
      yo lo situaría antes del With (On Error Resume Next)
      y después del End With (On Error GoTo 0).. pero ojo, lo que consigues con eso es saltar el error, es decir, una vez se genere un error (en la 10006 o la que sea) nada de lo siguiente se ejecutará...
      Saludos

      Eliminar
  72. Amigo Ismael mil gracias.
    resulto genial, pero, ayer en la tarde me surgió otro agujero, quizás puedas ayudarme.

    Es lo siguiente:

    existe la posibilidad de colocar dentro del el URL una regla que permita seguir en correlativo hasta el 10200? exactamente donde se encuentra la numeración 10006, y que permita que esta regla se repita hasta llegar el 10200.

    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://rncenlinea.snc.gob.ve/planilla/index/10006?anafinan=N&anafinanpub=Y&login=N&mostrar=INF" _

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías incorporar un bucle (tipo for x=10006 to 10200)
      e incorporar la variable dentro del literal

      for x=10006 to 10200

      With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://rncenlinea.snc.gob.ve/planilla/index/" & x & "?anafinan=N&anafinanpub=Y&login=N&mostrar=INF" _
      ......

      Saludos

      Eliminar
  73. Estimado tengo una duda. Si tuviera un rango por ejemplo de "$A$1:$A$10", en donde:

    A1(color negro), A2(color azul), A3, (color amarillo), A4 (color blanco), A5 (color blanco), A6 (color amarillo), A7 (color blanco), A8 (color blanco), A9 (color azul), A10 (color amarillo).

    Como haría para sumar las celdas del mismo color mediante una macro, es decir: La A1 = SUMA(A2,A9) , A3 = SUMA(A4,A5) , A6 = SUMA(A7,A8) , A9 = SUMA(A10).

    ResponderEliminar
  74. bueno dias tengo un problema con un libro de exel que posee 150 hojas y en la mayoria se agregan datos diariamente y es muy engorroso buscar todos los datos quisiera desde un userform poder mediante un listbox poder leer todos los datos que se agregaron en las distintas hojas ya que solo se agregan en la columna D de cada hoja espero puedan darme una mano gracias

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      para desarrollos completos mejor lee primero las Normas de uso del blog.
      En todo caso, la idea sería recorrer hoja por hoja la columna D que comentas para ir cargando el ListBox con su propiedad AddItem cada registro que cumpla las condiciones que le indiques.
      Saludos

      Eliminar
  75. Buenas noches Ismael, tengo una consulta. Quiero crear una macro en a cual pueda digitar una palabra "x" en todas las celdas que contiene una columna y esta me arroje automáticamente palabras en otras columnas "z" , "y" en todas las celdas que contiene esas columna como haría?

    ResponderEliminar
    Respuestas
    1. Hola Alonso,
      no comprendo muy bien que necesitas hacer.
      Pero me atrevo a decirte que necesitarás construir un bucle o loop, por ejemplo del tipo FOR NEXT que recorra las celdas de una columna, rellenando dichas celdas con la palabra "x"...
      al mismo tiempo, en las celdas de otras columnas, según alguna condición, usando IF... THEN rellenarías las celdas correspondientes.
      Siento no poder ser más específico
      Saludos

      Eliminar
  76. Como envio una duda????.
    Necesito ayuda con respecto a la utilización de la instrucción " . formula", ejemplo:
    sheets("hoja1").cells(1,1).formula="sum(c1:c2)".
    mi duda es como hacer que esos "c1" y "c2", sean variables del código y no elementos de la hoja, he visto que han concatenado con variable pero siempre utilizan algún elemento de la hoja.
    La otra vertiente de solución seria realizar una actualización permanente de las celdas mediante código(sin apretar un botón) me explico.
    Al utilizar las celdas de un libro puedo hacer que c1=sum(c2:c3), esto es así permanentemente hasta que se hace algún cambio en la formula, es decir si cambio los valores de c2 y c3 se me actualiza c1, quiero lograr este efecto con código(botón o formulario) pero sin la necesidad de utilizar el botón cada vez que quiera actualizar sino solo una vez, osea un botón del tipo "ACTIVAR".
    Espero puedan responder mi duda, gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      si quieres emplear una variable como parte de tu .formula, obviamente tendrás que asignar valor a esa variable, para lo cual tienes al menos dos alternativas:
      1-asignar valor a esa variable tomando dato desde la hoja de cálculo
      2-asignar valor a esa variable desde dentro del mismo código de la macro.

      Saludos

      Eliminar
  77. Antes que nada te felicito por este excelente blog que me viene siendo de mucha ayuda. Mi pregunta es la siguiente: no conozco mucho de Macros, y necesito emplearlas para desarrollar lo siguiente:

    Tengo una planilla en la que para una misma fila, tengo distintas formulas en celdas de las distintas columnas.Necesito una rutina que, a partir de que el usuario haya cargado determinados datos en celdas de dicha fila (que son parametros de entrada de las formulas), al correr la macro las formulas se ejecuten automaticamente en las celdas restantes de la fila, mientras que si el usuario no corrio la macros, estas ultimas celdas aparezcan vacias ("como si las formulas estuviesen ocultas y solo se activasen con la macros o algo asi") . Desde ya muchisimas gracias por tus aportes

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá lo más simple para replicar lo que necesitas sea convertir en Tabla (Insertar > Tabla) el rango de trabajo con esas fórmulas...
      así conseguirás lo que mencionas.
      Saludos

      Eliminar
  78. hola
    agradeceria me pudieras ayudar con la siguiente formula

    ActiveCell.FormulaLocal = "=+CONTAR.SI(CF2:CF1000,""CUMPLE"")"

    como se puede hacer para que el rango CF2:CF10 sea variable
    la formula me funciona como esta pero ese rango varia todos los dias
    ademas en la misma columna hay distintos tipos de datos

    se agradece la ayuda



    ResponderEliminar
    Respuestas
    1. Hola,
      revisa el post más arriba, solo replica el caso. Algo así:
      ActiveCell.FormulaLocal = "=CONTAR.SI(" & vble & ")"
      y con 'vble' podrás construir un literal que represente el rango que quieras.
      Saludos

      Eliminar
  79. Hola Ismael Romero

    Fíjate que he querido crear una macro que me arroje el subtotal de la suma de unos importes. este subtotal se actualizaría cada vez que se agregue o elimine un importe y el subtotal se colocaría justo debajo de la ultima fila que corresponde al ultimo importe.


    la macro que hice es esta:

    Private Sub CommandButton4_Click()


    Dim i As Integer


    Range("N30").Select



    While ActiveCell.Value <> "" 'Busca la ultima fila
    ActiveCell.Offset(1, 0).Select 'si la celda contiene datos, pasa a la fila siguiente
    Wend


    For i = 1 To 5 'en dado caso que sean n las celdas que quieres sumar
    Range("N31").Value = Range("N30").Value + Range("N" & i).Value

    Next i

    Range("N30").Select



    End Sub


    ahorita lo tengo en un boton, es solo para probrarla
    , pero no me arroja la suma de las celdas que se indican en el for i.


    ¿me podrías orientar?

    ResponderEliminar
    Respuestas
    1. Hola,
      si entendí bien, creo esto es lo que necesitas
      Sub sumanado()

      Range("A1").Select

      suma = ActiveCell.Value

      While ActiveCell.Value <> "" 'Busca la ultima fila
      ActiveCell.Offset(1, 0).Select 'si la celda contiene datos, pasa a la fila siguiente
      suma = suma + ActiveCell.Value
      Wend

      ActiveCell.Offset(1, 0).Value = suma

      End Sub

      Saludos

      Eliminar
  80. Muchas gracias Ismael, me sirvió.

    Saludos

    ResponderEliminar
  81. Hola Ismael,

    Fijate que he querido copiar una hoja, de un libro a otro, pero me marca este error:


    Se ha producido el error '9' en tiempo de ejecución:
    Subíndice fuera del intervalo.

    en la linea de codigo.





    Workbooks("PRECIOS PARA VENTA FORANEA.xlsm").Worksheets("pedido").Copy After:=Workbooks("PEDIDOS.xlsx").Sheets(Sheets.Count)

    ¿Podrias ayudarme?

    ResponderEliminar
    Respuestas
    1. Hola,
      primero revisa que todos los nombres de los Libros (origen y destino) están bien escritos (exactamente!), igual para los nombres de las hojas..
      Y finalmente asegúrate que en el Libro destino no existe una hoja con el mismo nombre 'pedido'
      Por lo demás el código es correcto
      Saludos

      Eliminar
    2. Ok. gracias y saludos

      Eliminar
  82. Buenos dias, tengo el siguiente codigo que intenta copiar el mismo rango desde el archivo "DATOS DEL SISTEMA" hacia el archivo "Escenario" en la hoja Datos, siempre y cuando el nombre de la hoja del primero sea igual al encabezado de columna de la hoja Datos del segundo archivo, la intención es que si falta alguna variable el proceso no se detenga y salte al siguiente con datos y luego en el archivo Escenario elimine las columnas que queden sin datos ya que no todos los dias hay dato para todas las variables esto cambia dia a dia necesito que en la hoja datos solo aparescan las columnsa en forma consecutiva con las que tengan información. El problema es que la macro me borra todo al final, al prinsipio me estaba funcionando bien pero no se qué paso, no lo quiere hacer me gustaría por favor si puedes revisarlas a ver si das con el error:

    Windows("Escenario.xls").Activate
    Sheets("Datos").Select
    Range("B2:O21").Select
    Selection.ClearContents

    Windows("Escenario.xls").Activate
    Worksheets("DATOS").Range("B1").Value = "30"
    Worksheets("DATOS").Range("C1").Value = "60"
    Worksheets("DATOS").Range("D1").Value = "90"
    Worksheets("DATOS").Range("E1").Value = "120"
    Worksheets("DATOS").Range("F1").Value = "180"
    Worksheets("DATOS").Range("G1").Value = "270"
    Worksheets("DATOS").Range("H1").Value = "360"
    Worksheets("DATOS").Range("I1").Value = "720"
    Worksheets("DATOS").Range("J1").Value = "1080"
    Worksheets("DATOS").Range("K1").Value = "2160"
    Worksheets("DATOS").Range("L1").Value = "4320"
    Worksheets("DATOS").Range("M1").Value = "5200"
    Worksheets("DATOS").Range("N1").Value = "USD"
    Worksheets("DATOS").Range("O1").Value = "LT"

    Range("B1:O1").Select
    Selection.Font.Bold = True


    Application.ScreenUpdating = False
    For Each celda In Hoja1.Range("B1:O1")
    Windows("DATOS DEL SISTEMA.xls").Activate
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name = celda.Value Then
    sh.Select
    Range("E4:E23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Escenario.xls").Activate
    Cells(2, celda.Column).Select
    ActiveSheet.Paste

    Application.CutCopyMode = False
    End If
    Next sh
    Next celda

    Application.ScreenUpdating = True

    Columns("B:O").EntireColumn.AutoFit

    Windows("Escenario.xls").Activate
    Dim n As Integer 'n° columnas
    Dim i As Integer
    n = ActiveSheet.UsedRange.Columns.Count
    For i = n To 1 Step -1
    Range("B2:O21").Select
    If ActiveSheet.Cells(2, i) = "" Then
    ActiveSheet.Cells(2, i).Select
    Selection.EntireColumn.Delete
    End If
    Next i
    [b2].Select
    MsgBox "Columnas Vacias Eliminadas"


    Range("B1", Range("B1").End(xlDown).End(xlToRight)).Select

    ResponderEliminar
    Respuestas
    1. Hola!
      yo revisaría los datos pegados en el libro Excenario.xlsx, y en concreto la fila 2...
      la condición que has puesto es que cuando esté sin datos se elimine la columna..
      podría ser no hubiera datos pegados en dicha fila 2

      Slds

      Eliminar
  83. Buenos tardes Ismael, gusto en saludarte, te escribo para ver si tú me puedes ayudar con una duda que tengo con una macro, tengo que copiar de varios archivos que se encuentran en un misma carpeta y cuyos nombres varían solamente es la fecha por ejemplo “Reporte Diario_02052017”, es decir lo único que cambia son los últimos 8 caracteres del nombre de estos archivos, de los mimos deseo copiar determinados rangos de datos que se encuentran en varias columnas, las columnas siempre serán las mismas (B14, F14, H14, I14, K14, L14 Y M14) hasta la última fila con valor, luego esos rangos los debe pegar en determinadas columnas en el archivo "BD INFORME MENSUAL DE VaR.xls" en la hoja "BASE DE DATOS". Te explico lo que hice y en donde están mis dudas, adapte la siguiente sintaxis que encontré, pero solo copiando el rango K14:M14 hasta la última fila con valor solo para ver si realiza el proceso, al correr la macro solo me está tomando los datos de un solo archivo en la carpeta, la del último día registrado (18052017), no me está copiando el del resto de los archivos dentro de la carpeta, que es lo que quiero, que de todos los archivos dentro de la carpeta copie el mismo rango de valores y los pegue en el archivo BD INFORME MENSUAL DE VaR.xls. Mi otra duda está en cómo debo hacer para que el rango B14 hasta la última fila con valor lo pegue en la columna A en la última fila disponible del archivo BD INFORME MENSUAL DE VaR.xls, el rango F14, H14, I14 hasta la última fila con valor lo pegue a partir de la columna D en la última fila disponible y el rango K14, L14 Y M14 hasta la última fila con valor lo pegue en la columna G en la última fila disponible. Esperando me puedas ayudar con la siguiente duda, quedo a la espera de tu siempre oportuna recomendación.

    Saludos cordiales



    Sub UnirDatosVersion2()

    Application.ScreenUpdating = False

    ruta = ThisWorkbook.Path

    ChDir ruta

    archi = Dir("*.xls*")

    Set h1 = ThisWorkbook.Sheets("BASE DE DATOS")

    nFilaFin = Range("a" & Rows.Count).End(xlUp).Row



    On Error Resume Next

    ffin = h1.UsedRange.Find(what:="*").Row

    ActiveCell.SpecialCells(xlLastCell).Select

    On Error Resume Next

    Do While archi <> ""

    If InStr(1, archi, "BD INFORME MENSUAL DE VaR") = 0 Then

    Workbooks.Open archi

    If Err.Number = 0 Then

    Sheets(1).Select

    Range("K14:M14", Range("K14:M14").End(xlDown)).Copy _

    h1.Range("G" & nFilaFin + 1)

    End If

    Err.Number = 0

    Application.DisplayAlerts = False

    Workbooks(archi).Close

    Application.DisplayAlerts = True

    End If

    archi = Dir()

    Loop

    End Sub

    ResponderEliminar
  84. Hola Salediano,
    muchas dudas para contestar en un comentario.
    mejor lee las Normas de uso del blog, y según éstas, sigue las indicaciones, si estuvieras interesado.
    Un saludo

    ResponderEliminar
    Respuestas
    1. Buenos días Ismael, disculpa lo extenso de la pregunta, me centraré en el principal problema y que es el más importante para mi resolver, el resto lo solucionaré yo por mi cuenta. El principal problema es que al correr la macro solo me pega el rango seleccionado del archivo con la última fecha dentro de la carpeta, no copia el del resto de los archivos que es lo que se busca, cómo debo hacer para corregir este problema, es decir que de todos los archivos dentro de la carpeta especificada se seleccione ese rango lo copie y pegue en la base de datos. Esperando haber sido un poco mas especifico me despido agradeciéndote de antemano tu ayuda para con la presente inquietud.

      Saludos cordiales.

      Eliminar
    2. Hola,
      creo que debes asegurarte qué libro está activo y de donde se está copiando.
      Echa un vistazo a este otro post:
      http://excelforo.blogspot.com.es/2012/10/vba-macro-para-abrir-y-cerrar-un-libro.html
      Saludos

      Eliminar
  85. Hola alguien podria decerime que esta mal en esta linea de codigo

    Range("J[" & n & "]:N[" & n & "]").Select

    n es un contador que quiero que varie en el programa.Cuando lo corro me da error y lo indica justo en la linea de codigo que aabo de pegar. de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      creo estás mezclando estilos de referencia, si quieres construir un rango del estilo:
      J1:N30
      te sobran los corchetes...
      Debería ser algo así:
      Range("J" & n & ":N" & n & "").Select

      Saludos

      Eliminar
  86. Hola otra vez soy yo... en otro foro me corrigieron y la forma correcta de escribir esta expresion hubiera sido:

    Range("j" & n & ":" & "n" & n).Select

    Sin embargo, me gustia hacer que el rango vaya bajando por la hoja de calculo a cierto ritmo y que a su vez inserte en celdas igualmente cambiantes la funcion ESTIMACION.LINEAL desde macros. Por lo que realice el codigo que pego a continuacion:

    Range("M" & (47 + 20 * i) & ":" & "P" & (47 + 20 * i)).Select
    Selection.FormulaArray = "=+LINEST(R[44 + 20 * i]C2:R[44 + 20 * i]C11,R[45 + 20 * i]C2:R[45 + 20 * i]C11^{1,2,3,4})"

    El problema es que cuando corro el programa me arroja un error. Que escribi mal?

    ResponderEliminar
    Respuestas
    1. Hola Daniela,
      como te indicaba en el comentario anterior, efectivamente una forma correcta de referirte al rango, era sin los corchetes:
      Range("J" & n & ":N" & n & "").Select

      En cuanto a tu fórmula probaría a emplear el estilo de referencia A1 en lugar de R1C1 que estás utilizando... seguro te simplifica el trabajo y se verá cuál es el error.
      El error podría estar en el exponente {1,2,3,4}.. donde quizá los separadores no sean los mismos que empleas en la hoja de cálculo, prueba con ; o / o incluso \

      De todas formas escribiré un post hablando del tema

      Saludos

      Eliminar
    2. Y como hago para cambiar de estilo referencia de RC a A1?

      Eliminar
    3. La semana que viene escribiré un post al respecto...
      Saludos

      Eliminar
  87. Hola.

    Estoy intentando ingresar la función Sumif de acuerdo a los métodos explicados en este foro, cuando corro mi macro en mi computador y en algunos otros no tengo problema alguno, sin embargo, cuando corro la macro en ciertos computadores me sale el famoso error 1004 en tiempo de ejecución. No entiendo por que está pasando esto. La función sé que está bien definida ya que de lo contrario no me correría ni siquiera en mi pc. ¿Por que puede estar pasando esto y como lo soluciono?

    ResponderEliminar
    Respuestas
    1. Hola David,
      a veces pasa que los separadores de los argumentos son diferentes según versiones de Windows o de Office, o incluso se trabaja con estilos de referencia distintos (A1 o R1C1 y F1C1 en español).. lo que provoca el error comentado...
      Habría que ver cómo lo tienes escrito en tu macro
      Saludos

      Eliminar
  88. Muchas gracias Ismael, efectivamente se trataba de los separadores en los argumentos.

    ResponderEliminar
  89. Hola,
    Consulta:
    La columna B tiene n números separados que se intercalan (en la misma columna) dos espacios y se debe generar la suma en el primer espacio encontrado, saltar el siguiente espacio en blanco y volver a calcular la otra suma hasta terminar cono todos los datos de la columna.

    ResponderEliminar
    Respuestas
    1. Hola
      una idea sería recorrer la columna (un bucle FOR EACH... NEXT, por ejemplo) e ir identificando la condición, celda a celda, si hay dato en la anterior y no en la siguiente, entonces insertar la fórmula o suma...

      Saludos cordiales

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.