jueves, 8 de septiembre de 2011

VBA: Macro para rellenar celdas en blanco en Excel.

En una entrada anterior vimos como trabajar con la herramienta Ir a Especial > Celdas en blanco para rellenarlas con un valor o fórmula concreta(ver).
El problema de esta herramienta es que para un número elevado de valores o celdas a rellenar puede fallar. Será en estos casos cuando debamos emplear las macros.
Partimos de un ejemplo similar a la del post anterior:

VBA: Macro para rellenar celdas en blanco en Excel.


Sabiendo que nuestros datos se encuentran en la 'Hoja1' y que el rango de celdas a evaluar comienza en A1, el código a incluir en un módulo del Editor de VBA (Alt+F11) será:

Sub RellenaCeldasenBlanco()
Dim hoja As Worksheet
Dim UltFila As Long
Dim Rng As Range

Set hoja = Sheets("Hoja1")
With hoja

'Encontramos la última fila con valores
UltFila = .Range("A" & .Rows.Count).End(xlUp).Row
'creamos el rango
Set Rng = .Range("A1:A" & UltFila)
End With

'formulamos las Celdas en Blanco para que tomen el valor de la celda anterior +1
Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"

'liberamos las variables de objeto
Set hoja = Nothing
Set Rng = Nothing

End Sub


El resultado será la introducción en cada una de las celdas en blanco del rango de la fórmula =R[-1]C+1, o lo que es lo mismo celda anterior más uno.

VBA: Macro para rellenar celdas en blanco en Excel.

242 comentarios:

  1. me gusta... gracias

    ResponderEliminar
  2. lo que pasa es que quiero rellenar con ceros ciertas celdas vacias
    cono hago ayuda..
    las celdas que se quiere rellenar estan por rangos..ayuda

    ejemplo

    d15:d30,e15:e30,f15:f30

    d45:d60,e45:e60,f45:f60

    esas celda quiero rellenar con ceros
    pero ciertas celdas en estos rangos pondre manualmente numero
    y no quiero que se remplacen los valores que coloque yo
    ayuda

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, tendrías que realizar una macro de este estilo, con un loop FOR EACH, ya que necesitas evaluar previamente si cada celda tiene algún dato:
      Sub rellenar()
      FOR EACH celda in Range("d15:d30,e15:e30,f15:f30,d45:d60,e45:e60,f45:f60")
      IF celda="" THEN celda.value=0
      NEXT celda
      End Sub
      Saludos

      Eliminar
    2. selecciona todo el rango dale ctrl + b cuando te aparece la ventanita de buscar, le das buscar siguiente, te buscaraceldas vacias, hay mismo sellecciona la pestaña reemplazar y dile que te lo remplace con 0 o con lo que quieras y le das reemplazar todos

      Eliminar
  3. Buenas Tardes,
    Quisiera saber como puedo rellenar unas celdas vacías con la palabra "NA" para unas columnas determinadas al momento de ingresar un valor en una celda,es decir, cuando ingrese una palabra en una celda, quisiera que un grupo de columnas se rellenaran automáticamente con "NA" y que otras queden vacías.

    ResponderEliminar
    Respuestas
    1. Hola Chechi,
      tendrías que utilizar en tu macro un IF... THEN, con un método INTERSECT que detecte el cambio en la celda que vayas a ingresar una palabra.
      Por ejemplo, si en A1 introduces el texto 'Excelforo' que el rango B1:B10 se complete con NA:
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Intersect(ActiveCell, Range("a1")) Is Nothing Then
      If Range("a1").Value = "Excelforo" Then Range("B1:b10").Value = "NA"
      End If
      End Sub

      a incluir en código en el explorador de VB en la hoja en cuestión.
      Slds

      Eliminar
  4. Me podrias ayudar. Necesito Ingresar datos en Excel, y no deseo que hayan celdas vacías, sino que todas las celdas tengan información, de lo contrario que aparezca un mensaje que hay que llenar datos. ¿Cuál seria la macro que debo de utilizar?
    Mil gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola!
      supongo que con todas las celdas te referirás a unas cuantas determinadas...
      Yo haría un procedimiento asociado a la Hoja donde estén esas celdas, por ejemplo 'Hoja1', que al cambiar a otra hojas ejecute la macro y verifique si las celdas en cuestión están o no completas.
      El código a incluir en la hoja del Explorador de VB sería de este estilo:
      Private Sub Worksheet_Deactivate()
      If Range("A1").Value = vbNullString Then
      MsgBox "Faltan datos!!"
      Sheets("Hoja1").Activate
      End If
      End Sub

      Que evalua si en la celda A1 de la Hoja1 hay o no datos, si está vacío al intentar cambiar de Hoja en el Libro saltará el mensaje y no te dejará cambiar de Hoja, activando nuevamente la Hoja1.

      Espero te sirva.
      Slds

      Eliminar
  5. Hola, no se si me puedas ayudar,
    necesito encontrar una forma para rellenar el espacio en clanco con la informacion de la celda anterior, en tu ejemplo se rellena con una serie, hay alguna forma de que en vez de serie se rellene lo que dice en la celda anterior.ej.
    A3 [01]
    A5, A6, A7 [03]
    A9, A10 [07]

    salu2
    nicole

    ResponderEliminar
    Respuestas
    1. Hola Nicole!
      bueno, no sé si quieres emplear macros o te sirve un método sencillo directamente en la hoja de cálculo.
      No veo claro tu distribución de celdas, pero supondré que en el rango A1:A10 tienes algunas celdas vacías...
      Lo primero que harás será seleccionar el rango, y presionar Ir a especial, seleccionar celdas en blanco. Se activarán las celdas sin datos, en la activa escribes la referencia de la celda inmediata superior, y presionar Ctrl+Enter en lugar de sólo Enter.
      Así, en cada celda vacía e copiará el valor de la celdas superior.

      Pruéblao y me comentas.
      slds

      Eliminar
  6. Ismael, mil gracias era justo lo que buscaba


    ^_^

    ResponderEliminar
  7. Necesito ayuda please, a ver si sé explicarlo:


    Necesito que me rellene datos en una columna pero a partir de la celda que sea 0 y 0 es cuando sea el máximo equivalente de otra columna, pongo un ejemplo:

    Tengo una columna con 1,2,3,5,3,2,1 y otra columna al lado vacía, entonces en la columna de al lado, necesitaría que en la casilla correspondiente al máximo de la otra columna se pusiera un 0 y se rellenara hacia arriba y hacia abajo, en este caso se tendría que quedar algo así: 3,2,1,0,-1,-2,-3......yo lo que he hecho es ponerle un condicional para que cuando la casilla de al lado sea máximo, me ponga un 0 y en las otras un 1, pero a partir de ahí ya no sé continuar...esto lo he hecho como una macro asociada a un botón que ejecuta otras a la vez(encadenadas), tengo que matizar que yo de programación sé más bien poco...

    ResponderEliminar
    Respuestas
    1. Hola L0p3z,
      en breve subiré una entrada explicando la fórmula en cuestión (sin macros).
      Un saludo

      Eliminar
    2. Oooookkkk!!! gracias!!!

      Eliminar
  8. Buen día, tengo un problema con una macro y quisiera pedir su ayuda.

    Necesito hacer una interpolación de valores, para esto tengo un archivo con tres columnas, la primera contiene la fecha de la observación, la segunda contiene el número de ésta, y la tercera contiene el valor de la observación.

    Tengo 4 celdas vacías entre cada observación, y éstas se deben de llenar mediante la interpolación de valores, por ejemplo:

    Fecha Ob Valor
    01/01/2013 1 10
    02/01/2013 2
    03/01/2013 3
    04/01/2013 4
    05/01/2013 5
    06/01/2013 6 4.5

    Lo estuve haciendo con una serie de ciclos For y con un step para que se repitiera cada determinado número (dado por el step)

    El problema surge ya que me percaté que no siempre se tiene 4 celdas vacías entre cada valor, por lo que el step ya no funciona, ya que borra los datos originales.

    Fecha Ob Valor
    01/01/2013 1 10
    02/01/2013 2
    03/01/2013 3
    04/01/2013 4
    05/01/2013 5
    06/01/2013 6 4.5
    07/01/2013 7
    08/01/2013 8
    08/01/2013 9
    10/01/2013 10 6.4
    11/01/2013 11
    12/01/2013 12
    13/01/2013 13
    14/01/2013 14
    15/01/2013 15
    16/01/2013 16 16.4

    La tabla de arriba es un ejemplo de lo que sucede con mi archivo; para resumir un poco, necesito hacer una interpolación que rellene las celdas vacías entre cada número.

    ¿Me podría ayudar a solucionarlo por favor?

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Leonardo,
      te ofrezco dos posibilidades.
      Una algo manual pero muy sencilla. Selecciona todo el rango de la columna Valor y desde Ir a especial... selecciona Celdas en blanco, tras aceptar verás que sólo tienes marcadas las celdas sin datos de la columan Valor, ahora sobre la celda activa que tengas (la primera de ellas) introduce tu fórmula de interpolación y al finalizarla, en vez de presionar Enter, presiona Ctrl+Enter.
      ASí tendrás completos los valores vacios, estén donde estén.

      La otra opción es aplicar un bucle FOR EACH...NEXT sobre el rango completo de Valor, aplicando una condición IF THEN:
      FOR EACH valor IN Range("Valor")
      IF 'valor'="" THEN formula_interpolación
      NEXT valor

      Espero te sirvan
      Slds

      Eliminar

  9. Hola,



    tengo un bucle con la propiedad listcont, pero también tengo datos en varios textbox y todos ellos van a la misma hoja de excel y necesito que se repitan tantas veces como el bucle list, y no conozco un textCount jejejeje...este es el código:

    Dim i As Integer
    Sheets("facturas").Activate
    If Trim(Range("A2").Value) = "" Then
    Range("A2").Activate
    Else
    Range("A1").End(xlDown)(xlDropDown).Activate
    End If
    ActiveCell.Value = Me.txtNoFactura.Text
    ActiveCell.Next.Value = Me.txtFecha.Text
    ActiveCell.Next.Next.Value = Me.txtRazon.Text
    ActiveCell.Next.Next.Next.Next.Next.Next.Next.Value = Me.txtObservaciones.Text
    'este el bucle que menciono y los datos de arriba (text)son los que necesito

    'poner tantas veces como el listcount cuente
    'y este es el bucle for del list
    For i = 0 To Me.lstCantidad.ListCount - 1
    lstCantidad.ListIndex = i
    lstDescripcion.ListIndex = i
    lstPrecio.ListIndex = i
    lstImporte.ListIndex = i
    ActiveCell.Next.Next.Next.Value = Me.lstDescripcion.List(i)
    ActiveCell.Next.Next.Next.Next.Value = Me.lstPrecio.List(i)
    ActiveCell.Next.Next.Next.Next.Next.Value = Me.lstCantidad.List(i)
    ActiveCell.Next.Next.Next.Next.Next.Next.Value = Me.lstImporte.List(i)
    descontarExistencia Me.lstDescripcion.List(i), Val(Me.lstCantidad.List(i))

    ActiveCell(xlDropDown).Activate

    Next



    Espero puedas ayudarme con esto.... de antemano muchas gracias



    Dzea

    ResponderEliminar
    Respuestas
    1. Hola!
      disculpame, pero no termino de comprender qué necesitas...
      tienes
      ActiveCell.Value = Me.txtNoFactura.Text
      ActiveCell.Next.Value = Me.txtFecha.Text
      ActiveCell.Next.Next.Value = Me.txtRazon.Text
      ActiveCell.Next.Next.Next.Next.Next.Next.Next.Value = Me.txtObservaciones.Text que quieres se repitan tantas veces como el bucle
      For i = 0 To Me.lstCantidad.ListCount - 1


      Si es así, sólo tendrías que incluirlo dentro del bucle, como has hecho para .List

      Si pudieras aclararme este punto???
      Slds

      Eliminar
    2. Gracias por responderme tan pronto, verás... tengo una tabla donde se reciben estos datos y como notaras segun el codigo todos van a la misma tabla, pero cuando pongo los datos de los textbox dentro del bucle for... me salta error diciendo que la propiedad no corresponde, la verdad me rebano los cesos pues hasta donde entiendo al hacerlo así deberia funcionar, será que tendré algún error en el bucle? o en alguna otra parte del codigo? quizas veas algo que yo no...
      De nuevo gracias por tu colaboración
      Saludos

      Eliminar
    3. Hola diego,
      mejor adjúntame el fichero y envíamelo a
      excelforo@gmail.com

      Cuando lo vea 'in situ' te podré decir algo...
      Slds

      Eliminar
  10. Hola, disculpa, tengo una tabla de ranking con 4 columnas: posición, nombre, puntos y desempate, y lo que quiero lograr es que se rellenen únicamente con color esas 4 celdas de cada fila en las cuales el valor de la columna puntos esté duplicado, y ademas que cuando los valores duplicados sean diferentes también el color lo sea. Ejemplo:

    POSICION NOMBRE PUNTOS DESEMPATE
    3 Carlos 30 1 <<<<< COLOR 1
    6 Jose 28 2 <<<<< COLOR 2
    7 Maria 10
    4 David 30 2 <<<<< COLOR 1
    1 Luis 50
    2 Julio 49
    5 Andres 28 1 <<<<< COLOR 2

    Datos Adicionales: La columna "DESEMPATE" la lleno manualmente para cuadrar las posiciones, o sea que en un principio no habrá datos alli
    y la cantidad de duplicados distintos en la columna "PUNTOS" puede llegar a ser indeterminado en algún momento.

    Es pero me puedas ayudar con esto y de antemano muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola DCG,
      podrías aplicar de una manera sencilla el formato condicional para valores duplicados, es un estándar del formato condicional.
      El problema es que no distinguiría por colores.
      Para conseguir esto habría que trabajr una macro que reconociera elementos comunes y le asignara un color.
      Puedes ve algo parecido, no es lo que buscas pero quizá te oriente, en
      http://excelforo.blogspot.com.es/2012/04/vba-macro-para-un-formato-condicional.html
      Slds cordiales

      Eliminar
    2. Muchas gracias Ismael, me sirvió mucho ese post que me recomendaste, ya encontré la forma de hacer que me distinga por colores diferentes, pero el color solo me queda sobre una misma columna. ¿Existe alguna manera, mediante codigo vb, para que me coloree las celdas de las columnas contiguas a la del rango que se le especifica?

      Eliminar
    3. Hola DCG,
      podrías aplciar color además de la celda que cumpla la condición a las contiguas, si son pocas (en tu caso tres más), con la instrucción .Offset(0,1).
      Por ejemplo,
      Range("A1").Offset(0,1)
      se refiere a la celda una columna a la derecha y la misma fila de la celda A1 (o sea la celda B1.
      Igual con .Offset(0,2) .Offset(0,3) etc dos o tres columnas a la derecha...
      aplicándole las mismas instrucciones de color.

      Espero te sirva.
      Slds

      Eliminar
  11. Hola Ismael, te pido ayuda con lo siguiente:
    Tengo una tabla de excel que debo estar alimentando y necesito que ciertas columnas me cumplan con un tamaño determinado de caracteres, es decir, que si escriben JAIME me la rellene hasta 10 caracteres ejm: "JAIMExxxxx" pero siendo las "x" solo espacios. Dichas columnas son tipo texto.
    Espero me puedas ayudar.

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      desde luego la acción de completar hasta esos 10 caracteres se tendría que hacer siempre a posteriori.

      Tienes dos opciones, una primera con funciones, pero en una columan auxiliar:
      =SI(LARGO(A2)<10;A2&REPETIR(" ";10-LARGO(A2));A2)
      pero claro, al ser fórmula, luego tendrías que copiar y pegar como valores....

      Otra opción sería con macros, de una manera similar a la anterior, podrías construir una amcro que evalúe las celdas en cuestión.. más o menos:
      If Len(ActiveCell) < 10 Then ActiveCell = ActiveCell & Space(10 - Len(ActiveCell))

      Espero te sirva alguna de las propuestas.
      Slds cordiales

      Eliminar
  12. Que tal Ismael queria ver si me puedes ayudar yo no se nada en cuestion a macros pero quiero ver de que manera puedo hacer para llenar con "10(x)" una celda que este vacia pero este documento de excel se va a estar cambiando constantemente por lo cual son variables los datos y se llenara por filas aqui te envio un ejemplo de como es la tabla :

    No.......titulo ....denominacion....cantidad
    1........blablab.......PSNS02...........121
    2.........blablab.......PSNS05...........15
    3.........blablab.......PSNS45...........132
    4........________......._________.......___

    Aqui la fila con esos 3 datos restantes que me ponga XXXX... al igual que en las demas filas que me hicieran falta sigue en consecutivo pero la tabla tiene un tope de datos
    PD: en la parte de cantidad tiene un candado de solo poner numeros no se si afecte o si le pudiera poner una linea

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías crear una macro sencilla como esta:
      Sub macro()
      For Each celda In Range("A1:C3")
      If celda.Value = "" Then celda.Value = "XXXX"
      Next
      End Sub

      y ejecutarla para completar las celdas vacías por lo que quieras..
      Slds

      Eliminar
  13. hola este necesito hacer un macro que pueda copiar la informacion de otras tablas que estan en un solo libro y hacer una sola lista en otro libro si me entienden me podrian ayudar muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías aplicar lo explicado aquí
      http://excelforo.blogspot.com.es/2009/10/macro-de-un-filtro-avanzado-en-excel.html

      con mínimas modificaciones para corregir dónde copiarlo. Es algo simple pero efectivo.

      Slds cordiales

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

    ResponderEliminar
  15. hola veo que eres excelente en vba..
    si pudieras ayudarme te lo agradeceria un monton...
    buen dia..

    ResponderEliminar
    Respuestas
    1. Hola,
      sigo aprendiendo cada día...
      Puedes plantear tu cuestión, y si está en mi mano responder tu duda lo haré
      Slds

      Eliminar
  16. hola me puedes ayudar lo que pasa es que quiero saber si se puede bincular de alguna manera excel con el correo de hotmail esto para enviar correos a una lista de personas que tengo un una base de datos

    ResponderEliminar
    Respuestas
    1. Hola,
      si es posible, pero mediante programación (macros).
      Echa un vistazo a
      http://msdn.microsoft.com/en-us/library/office/ff458119(v=office.11).aspx

      Slds

      Eliminar
  17. hola!! un saludo cordial
    quisiera saber si me podrias ayudar con una funcion para VBA: necesito que una celda en blanco se rellene de un color especifico cuando le pongo la palabra "asistio" o y de un color diferente cuando le ponga la palabra "reposo" y asi con varios escenarios..
    Muchas gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola Carmelo,
      para eso no se necesita una acro o función VBA, basta con que apliques reglas de Foramto condicional sobre esas celdas, tantas reglas como 'escenarios' o condiciones tengas...
      Echa um vistazo a
      http://excelforo.blogspot.com.es/search/label/Formato%20condicional

      Espero te sirva
      Slds

      Eliminar
  18. Hola Ismael,
    Primero quiero felicitarte por tu Blog, y luego molestarte con un tema que es muy simple pero hasta ahora estoy comenzando a usar VBA y no tengo claro como programarlo.
    Tengo una serie de formulas en la primera fila iniciando en B1 y quiero que me las copie hacia abajo pero solo si la celda de la columna A tiene datos (como ocurre con el doble click) pero la cantidad de filas con datos en A puede variar. (necesito hacerlo en VBA porque es solo una parte de la macro)
    Gracias por tu colaboraicon

    ResponderEliminar
    Respuestas
    1. Hola Jorge, muchas gracias!
      Yo intentaría un bucle FOR NEXT en la primera columna (columna A), que será la que determine si se copia o no la fórmulas de B1, C1, etc...
      dentro de ese bucle anidaríamos un IF THEN que determine si la celda de la columna A está vacía o no, en caso de que sea cierto (esté vacía) no copiamos la fórmula.
      La estructura podría ser algo así:
      FOR EACH celda IN Range("A1:A100")
      IF celda<>"" THEN
      'código de copiado en celda.offset(0,1) y celda.offset(0,2), etc
      END IF
      NEXT celda

      Espero haberte orientado
      Slds

      Eliminar
  19. hola Ismael que tal como estas un mega favor no secomo hacer la siguiente macro en VBA es un gatoreto
    Requiero hacer una macro que busque un nombre (Hoja 1)en la columna A por ejemplo (Carlos) y que al mismo tiempo en la columna B tome el valor que tiene al lado es decir:

    Columna A Columna B
    Carlos 8
    Roberto 10
    Carlos 4

    y que sume el valor del numero las veces que se repita el nombre de Carlos.

    Lo interesante es que la suma del valor se distribuya en unos como una matriz en una Hoja 2 en un rango de 5 filas a lo largo de todas las columnas es decir si tengo una suma de 12

    Columna A B C D E F
    1 1 1 1
    2 1 1 1
    3 1 1
    4 1 1
    5 1 1
    y se actualice automáticamente cuando la base de datos de la hoja 1 se vaya modificando.
    Gracias por tu atención a mi solicitud saludos

    ResponderEliminar
    Respuestas
    1. Hola!
      no veo necesario el uso de macros para esto, bastaría aplicar una combinación de funciones sencillas:
      FILAS y COLUMNAS
      empleado sobre un rango auxiliar para luego aplicar un condicional sobre la suma acumulada obtenida para el elemento en cuestión (Carlos en el ejemplo).

      Escribiré un post en el blog más adelante explicándolo.
      Saludos

      Eliminar
    2. ok gracias bueno pues te lo agradecería mucho saludos

      Eliminar
  20. hola ojala me puedas ayudar
    lo que pasa es que tengo que compartir un libro con otros usuarios pero no todos tienen que ver la misma informacion y quiero saber si de alguna manera puedo proteger el libro para que dependiendo del usuario se muestre la informacion ojala que me puedas ayudar
    saludos y muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que programar una especie de menú inicio que identifique al usuario y muestre a uno u otro usuario las hojas que les correspondan (protegiendodo el resto)...
      Espero te oriente la solución.
      Slds cordiales

      Eliminar
  21. HOLA OJALA ME PUEDAS AYUDAR TE PLATICO UN RFC DE PERSONA FISICA SE COMPONE DE CUATRO LETRAS Y UN RFC DE PERSONA MORAL SE COMPONE SOLO DE 3 Y NECESITO EVALUAR ESA PARTE PARA QUE ME ARROJE UN "PERSONA FISICA" SI EL RFC TIENE 4 LETRAS O PERSOLNA MORAL SI TIENE SOLO TRES LETRAS OJALA ME PUEDAS AYUDAR

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías aplicar una función condicional de este tipo:
      =SI(LARGO(RFS)=3;"Persona moral";SI(LARGO(RFC)=4;"Persona física";"Otros"))
      Slds

      P.D.: por favor, evita escribir en mayúsculas.. es como si me gritaras

      Eliminar
  22. Hola, te felicito por tu blog es muy educativo, y me gustaría saber como hacer una macro que me coloque una cierta cantidad de ceros dependiendo de el entero, me explico la longitud total de los dígitos es 8. Sabiendo esto si tengo un 1 quiero que me coloque 7 ceros ala izquierda de le 1, si tengo 23 quiero 6 ceros y así sucesivamente.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias!!
      realmente para esto no es necesario construir una macro, basta aplicar un formato de celda personalizada con el tipo:
      00000000
      es decir, 8 ceros, que son los dígitos que siempre quiers que aparezcan.

      Saludos

      Eliminar
  23. sip, esa me la se, me explicare mejor con relación a lo que deseo:
    Tengo un código alfanumérico de 19 caracteres, donde los primeros 11 son constantes (no varían) y los otros 8 son los que te pido, necesito generar esos 8 dígitos de tal manera que yo pueda alojarlo en una variable para luego concanetarlos con los otros 11 caracteres y alojarlos en una determinada celda.

    Gracias.

    ResponderEliminar
    Respuestas
    1. Ok, entonces puedes aplicar la siguiente fórmula, suponiendo en C1 los dígitos...
      =SI(LARGO(C1)<8;REPETIR("0";8-LARGO(C1))&C1;C1)
      lo que obtendrás será un 'texto' que luego podrás concatenar con los 11 primeros caracteres.
      Espero te sirva.
      Saludos

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

    ResponderEliminar
  25. Enhorabuena por el aporte, es de gran utilizadad!!!
    nunca me atreví a escribir por si mi pregunta era muy básica, pero creo que esta es interesante.
    Como podría hacer si mis valores no son correlativos, subieran y/o bajaran casi de forma aleatoria y el espacio entre ellos también es variable y tuviera que interpolar cogiendo el último valor (orden descendente) y el siguiente. Ejemplo
    Ejemplo
    1 97
    2 97
    3
    4
    5 96
    6 96
    7
    8
    9 98
    10 98
    11
    12
    13
    14 98
    15 98

    Si hubiera algún post relacionado, diculpad mi osadía por repetir
    muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola José,
      lo primero decirte que por supuesto no hay ningún problema en que plantees tus dudas, por muy 'básicas' que te puedan parecer...

      Respecto a la cuestión que planteas lo primero que había que definir es qué tipo de interpolación aplicarías entre los dos valores que cierran el intervalo de celdas vacías...
      En todo caso no sería la herramienta empleada en este post, ya que no sirve una regla basada en el valor anterior...

      Pensaré una solución suponiendo una interpolación tipo media, por ejemplo, entre el punto 2(97) y 5 (96) devolvería 96,5...
      En cuanto pueda subiré una explicación en el blog.

      Un cordial saludo

      Eliminar
    2. El tipo de interpolación es gradual, perdón por no especificarlo.
      Es decir, debería calcular la diferencia y dividir por el número de celdas, de tal modo que en el ejmplo anterior, de 97 pase a 96.6777, 96.333 y finalmente 96, qu es el valor que ya disponemos.
      Cuando lo hago manual lo hace automaticamente si cojo la ultima celda con valores y la primera de la siguiente tanda...pero claro estamos hablando de miles de datos para cada archivo.
      muchas gracias por tan pronta respuesta.

      Eliminar
    3. Ok, José...
      estoy con ello, en breve subo el código.
      Slds

      Eliminar
  26. Hola!!!! como estas oye molestando como siempre
    lo que pasa es que recuerdo que puedo delimitar un rango con nombres definidos es decir tengo una base que estoy alimentando cada dia y de ella genero alguans tablas dinamicas pero diario tengo que cambiar el origen de datos de la tabla y recuerdo que con un nombre definido se podia evitar el estar cambiando el origen de datos me puedes ayudar por favor

    ResponderEliminar
    Respuestas
    1. Hola,
      realmente lo más sencillo sería seleccionar el origen de datos (tal cual lo tengas en este momento) y convertirlo en Tabla (desde el menú Insertar > grupo Tablas > botón Tabla).
      Con el origen convertido en tabla (a la que Excel asignará un Nombre, que puedes cambiar) ya puedes Resumir en una Tabla dinámica... o si ya tienes creada la TD cambia el origen de datos y pon el nombre de tu Tabla...

      Así conseguirás lo que quieres.

      Slds

      Eliminar
  27. Hola Ismael !
    Una extensa felicitación por estas grandes soluciones que das, en realidad eres un experto!. Bueno, resulta que tengo una lista de productos en una Hoja (“lista”) , deseo poner un botón en un espacio de un panel no movible que, cuando lo pulse, los datos de la celda donde tengo el cursor se me copie hacia otra Hoja y que se vaya almacenando en un listado vertical de manera automática. Te agradecería mucho tu ayuda . Gracias.

    Isidro =)

    ResponderEliminar
    Respuestas
    1. Muchas gracias Isidro, agradezco tus palabras ;-)

      Respecto a tu cuestión, la macro sería muy sencilla, y se debería definir una variable que determine cuál es la última celda de la hoja destino empleada.
      Quedaría:

      Sub copiar()

      destino = Sheets("Hoja2").Range("A" & Rows.Count).End(xlUp).Address
      Selection.Copy Destination:=Sheets("Hoja2").Range(destino).Offset(1, 0)

      End Sub

      suponiendo la 'Hoja2' en su columna A es donde queremos llevar el dato de la celda activa.

      Saludos

      Eliminar
    2. Gracias Ismael

      ok, me queda entendido la forma de esta macro para copiar de "hoja 1" a "hoja 2" ... pero cuando se copie en la "Hoja 2" ... ¿el copiado sería en una sola celda? , pues necesitaría que se fuera copiando en una columna "A" pero de manera sucesiva hacia abajo. =) gracias por tu atención.

      Atte: Isidro

      Eliminar
    3. Hola Isidro,
      prueba el código que te comenté...
      los copiados se harán en la columna A de manera sucesiva hacía abajo.
      Saludos

      Eliminar
  28. HOLA EXCELENTE, AYUDA. Y PERDONA SI HAN HECHO LA PREGUNTA ANTES PERO COMO LO PUEDO APLICAR CON TEXTO?

    ResponderEliminar
    Respuestas
    1. Gracias Erick..
      a qué te refieres con texto???...
      lo explicado en el post aplica cuando hay celda vacías a completar, si ya tuvieran texto.. con qué las rellenarías???
      Se trata de algún tipo de reemplazamiento??

      Saludos

      Eliminar
    2. Gracias a tu respuesta tan pronto y disculpa lo poco especifico de mi duda, tengo columnas de texto que quedan con celdas en blanco que se deben llenar con el texto previo.
      ejemplo
      pedro paramo
      (celda vacía)
      Luis armando
      (celda vacía)
      (celda vacía)
      La primera celda vacía debe llenarse con pedro paramo, y las siguientes dos con Luis armando. Se que es un llenado sencillo pero son listas muy largas y manejo muchos documentos por lo que quisiera aplicar una macro.
      Gracias de antemano.

      Eliminar
    3. OK...
      en ese caso si quieres emplear una macro, puedes reemplazar en la que se explica la línea 16
      por
      Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

      Saludos cordiales

      Eliminar
    4. Buen día te agradezco la solución: la tenia a un cambio, voy aprendiendo, ahora tengo dos dudas, en la primera estoy trabajando, para un macro que ejecute una tabla dinámica, logro que funcione pero máximo dos veces y después marca error, y deseo que trabaje en cualquier documento (debo hacer dos diferentes acomodos con los datos originales en la tabla dinámica siempre los mismos y es lo que busco haga el macro repetir ese orden de acomodo de datos)
      La segunda apenas surgió buscando un programa para unir documentos Excel, vi que se puede hacer una macro para ello, pero no encontré especificaciones al respecto, y tu manera de explicar me ha ayudado mucho.
      Buen día.

      Eliminar
    5. Hola Erick,
      trabajar desde macros con tablas dinámicas siempre es complicado.
      Cuando te refieres que 'ejecute una tabla dinámica' quieres decir que la cree o que la actualice??
      Ten presente que al crea una Tabla dinámica, se le asigna un Nombre por defecto, por lo que deberás controlar dicho nombre para que no genere error la segunda vez...
      por ejemplo, puedes forzar el cambio de nombre de la TD...
      Te recomiendo emplees el asistente de grabación y veas qué instrucciones usa.

      A la segunda cuestión te contesto en el comentario de más abajo.
      Slds

      Eliminar
    6. Gracias por tu tiempo, lo unico que busco que haga la macro con la tabla dinamica, es que la cree, o que simplemente me de el orden que necesito que siempre es el mismo en cada documento.
      y del segundo si es copiar cada documentto que abra y se copien en uno solo, ya que son muchos y debo estar trabajando con otros, me quita mucho tiempo, pero a que se refiere que no encuentra el objeto.
      y regresando a lo que me trajo aqui, en un principio =), como puedo hacer que funcione en cualquier hoja o columna que seleccione, y llenar las celdas vacias.
      Te podria mandar la macro de copiado que hice?
      Mil gracias, Buena Tarde.

      Eliminar
    7. Hola Erick,
      si, claro, puedes enviarme el fichero a
      excelforo@gmail.com

      Slds

      Eliminar
  29. Que tal tengo casi la macro para unir los documentos no se si con la siguiente línea me puedas ayudar, corre bien hasta esta línea, me indica error 424 no se encuentra objeto,
    abre los documentos abre y selecciona para copiar pero ya no pega en el documento que abro para que los una.
    Set ArchivoActual = ActiveWorbook

    ResponderEliminar
    Respuestas
    1. Hola Ercik,
      imagino los diferentes documentos hacen referencia a diferentes Libros de trabajo, y el proceso consistirá en ir abriendo los libros concretos y seleccionando un rango dentro de una hoja e ir pegando en otro...
      En ese caso es habitual emplear:
      Workbooks("Destino.xlsx").Sheets(1)
      para trabajar y poder copiar.

      en general te diría que tengas mucho cuidado con las rutas empleadas y cómo trabajas con ellas para referirte al rango copiado y el destino para el pegado...
      Añade las descripciones completas de Workbook y Sheets y Range..

      Saludos

      Eliminar
    2. Como no deseo que pienses que solo espero me resuelvas todo, he estado trabajando, al fin corri el programa para consolidar el programa para unir documentos de excel, y por pruebas que llevo creo que el macro para las tablas dinamicas corre bien , claro sigiuiendo tus consejos, mil gracias

      Eliminar
    3. ;-)
      me alegro!!
      saludos cordiales

      Eliminar
  30. Gracias por está gran ayuda y por compartir con nostros sus valiosos conocimientos, felicidades.
    Una consulta como puedo ingresar en la columna vacia pero que no copie la formula anterior solo que repita el ultimo dato de la columna? o sea con esta función " =I8 " suponiendo que en la fila o sea la I8 copie el número que esta en la fila I8 ... graciass
    Saludos cordiales
    Jesús

    ResponderEliminar
    Respuestas
    1. Hola Jesús,
      bastaría reemplazar el código de la línea 16 del post
      Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"

      por
      Rng.SpecialCells(xlCellTypeBlanks).Formula = "=I8"

      y si no siempre fuera la última celda la I8, aplicar una variable
      Puedes ver un ejemplo en
      http://excelforo.blogspot.com.es/2011/02/vbainsertar-una-funcion-con-macros.html

      Saludos

      Eliminar
  31. Hola:
    Como puedo hacer que funcione la macro de llenado en cualquier hoja o columna que seleccione, y llenar las celdas vacías.
    Gracias Buen Fin

    ResponderEliminar
    Respuestas
    1. Bastaría con que no mencionaras la hoja, sólo el Range...
      y si quieres que funcione en cualquier columna, deberías hacer mención en vez de Range ("A"....
      Selection....

      Saludos cordiales

      Eliminar
    2. perdona la ignorancia, pero no entendí bien la instrucción , podrías mandarme el macro armado, (peras y manzanas), disculpa
      Mil gracias

      Eliminar
    3. Haz una cosa,
      emplea el asistente de grabación y verás como el código que te aparece será, en lugar de con Range("D7") con Selection.
      Sólo sustituye los rangos y hojas por Selection.

      Slds

      Eliminar
  32. Que tal de nuevo,=) ,, sabrás si hay algún modo de ordenar datos contenido en una misma celda.
    Ejemplo, 1, 2, 6, 2, 10, 9 (en una celda) deben quedar de menor a mayor, habrá alguno modo o macro para organizarlos.

    ResponderEliminar
    Respuestas
    1. Hola Erick, de nuevo.
      podrías dividir con la función VBA Split los caracteres de la celda, luego intentar aplicarle una ordenación.. esto puede ser lo más complicado.
      una vez conseguida la ordenación con la función Join volverlo a unir...

      Para ordenar puedes leer esta entrada
      http://excelforo.blogspot.com.es/2012/12/vba-algoritmo-de-ordenacion-tipo.html

      Saludos

      Eliminar
  33. Agradeciendo nuevamente tu atencion, y pronta respuesta, hasta aqui ya casi todo resuelto, me falta lo de separ y unir, pero eso esmenos urgente, lo que si no he podido afinar es copiar un formato de un documento base que uso a cada uno de los documentos que debo armar,con este formato final,..y con la grabadora no he logrado concretar el procedimiento,

    ResponderEliminar
    Respuestas
    1. Hola Erick,
      creo que deberías leer las Normas de uso del blog.
      Si necesitas más ayuda para finalizar el desarrollo de tu proyecto ponte en contacto a través del email.
      Un cordial saludo

      Eliminar
  34. Hola Ismael.
    Nuevamente te quiero molestar con una duda, que no la puedo resolver.
    manejo un libro con 156 hojas de empleados que trabajan por turnos, y mes a mes se calculan montos.
    el problema es que a partir de la fecha de fin de mes necesito que se copie un rango de la hoja datos e todas las hojas.
    Otro problema es que como los turnos a veces se repiten puede exixtir dos veces 30 o 31 segun el mes
    en fin la macro debería reconocer la ultima celda con fecha 30/06/2014 0 31... según el mes, en el rango de d11 a d55 y partir de la siguiente celda copiar el contenido de la hoja datos en todas las hojas excepto la hoja datos.
    La fecha de fin de mes puede varias en todas las hojas, como puede estar en d35, o d45 o d47 o d53.
    ejemplo

    hoja1

    celda c4 mes junio
    ..
    ..
    d11 lunes,01
    d12 martes,02
    d13, miercoles, 03........
    d45 jueves,30


    hoja2

    celda c4 mes junio
    ..
    ..
    d11 lunes,01
    d12 martes,02
    d13, miercoles, 03........
    d52 jueves,30
    d53 juves,30


    ResponderEliminar
    Respuestas
    1. Hola Oscar.
      mejor lee primero las normas de uso del blog, y si te interesa envíame la consulta a:
      excelforo@gmail.com

      Un cordial saludo

      Eliminar
  35. Hola Ismael,
    Antes que nada muchas felicidades y gracias por ayudarnos eres un master!!
    Me gustaria pedir tu apoyo ya que requiero una formula o una macro que me ayude a buscar un valor en la fila d y lo coloque en la fila b
    ejemplo
    b c d e
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Pedro V1243 P.D
    lunes n 143
    martes n 243
    miercoles p 5143
    martes p 4243
    Isac V8364 I.D.
    miercoles n 51943
    martes f 42403
    lo que me gustaria hacer es que cuando se encuentre el valor con v lo coloque en la fila b pero solo hasta donde termina en este caso lunes,martes,mierco, etc les coloque el numero y despues cuando encuentre el otro ponga el otro numero en el mismo orden.
    espero poderme dar a entender y puedas ayudarme.
    muchas gracias de antemano.


    ResponderEliminar
    Respuestas
    1. Gracias Bloer!
      mejor envía el fichero y las explicaciones con un ejemplo a
      excelforo@gmail.com

      Un cordial saludo

      Eliminar
  36. Hola. agradecerle por anticipado su valioso apoyo. vengo manejando una base de datos en excel de utiles de escritorio cada una de ellas codificado. La base de datos tiene 40 columnas desde la "A" hasta la "AN" y las filas van en aumento de acuerdo a los ingresos que se tiene. Los codigos de utiles de escritorio ingresados mediante un useform se repiten de acuerdo a las fechas de ingreso, quiere decir que un mismo codigo puede tener varias fechas y otros terminos en las columnas señaladas, ademas en la columna "C" estos ingresos tienen la denominacion de "ING".
    Lo que quiero es mediante el useform ingresar un codigo, y este sea buscado en la base de datos y copie todos los codigos que coinsidan y tengan la denominacion de "ING" al final de la misma base de datos, pero que reemplase la denominacion de "ING" por "SAL" que quiere decir SALIDA.
    Atentamente.

    ResponderEliminar
    Respuestas
    1. Hola Ruben,
      puedes emplear para la búsqueda el método .Find, y emplear los comodines estándar asterisco * o cierre interrogación ?, por ejemplo, *ÎNG significa que termina en ING el texto buscado.

      Para el reemplazamiento emplea la función VBA: Replace...

      Saludos

      Eliminar
    2. gracias x su pronta respuesta, la verdad recien estoy empezando a entender las rutinas y la programacion que se hace en VBA, agradeceria me pueda ayudar para entenderlo mejor con una rutina de ejemplo de busqueda del codigo, seleccion y copiar al final de la base de datos, al momento de copiar las filas de los codigo que coinsiden que cambie en la columna C la denominacion ING por SAL. gracias nuevamente.

      Eliminar
    3. Hola Ruben,
      mejor lee primero la Normas de uso del blog, y si quieres envíame a
      excelforo@gmail.com
      un fichero con la plantilla de datos que tengas.

      Un saludo

      Eliminar
    4. Muy agradecido por tu ayuda, ya me diste una mejor idea. gracias, gracias...

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

    ResponderEliminar
  38. QUISIERA SABER COMO RELLENAR DE UN COLOR TODAS LAS CELDAS DE UN RANGO SI EN UNA DE ESTAS CELDAS DICE UNA PALABRA (ALGO COMO UN FORMATO CONDICIONAL PERO QUE RELLENE VARIAS CELDAS Y NO SOLO UNA). POR EJEMPLO:

    RELLENAR DE COLOR AMARILLO EL RANGO A4:BL4 SI EN LA CELDA M4 DICE LA PALABRA "JORGE"
    RELLENAR DE COLOR VERDE SI DICE "OSCAR"
    RELLENAR DE COLOR AZUL SI DICE "ANTONIO"

    Y ASÍ SUCESIVAMENTE CON VARIOS NOMBRES...

    ESPERO HABERME EXPLICADO CORRECTAMENTE.
    GRACIAS POR SU AYUDA.

    ResponderEliminar
    Respuestas
    1. Hola Jose Luis,
      pues se puede aplicando formatos condicionales.

      Primero selecciona aquel rango que quieras dar color, en tu ejemplo A4:BL4.
      A continuación de ja activa la celda M4, puedes moverte con el tabulador hasta ella.
      Ahora entra en el Formato condicional tipo fórmula e inserta:
      =$M4="JORGE"
      y aplicar un color.
      Repite la operación tantas veces como nombres o reglas quieras aplicar.

      Ya lo tendrías.

      Saludos

      Eliminar
    2. MUCHISIMAS GRACIAS POR TU AYUDA.
      FUE MUY UTIL.

      Eliminar
  39. CON RESPECTO AL COMENTARIO ANTERIOR ¿COMO PUEDO COPIAR LA FORMULA DEL FORMATO CONDICIONAL, PARA QUE APLIQUE A TODAS LAS CELDAS DE LA COLUMNA "M", SIN NECESIDAD DE HACER UNA FÓRMULA PARA CADA CELDA?...
    DISCULPEN MI IGNORANCIA...
    GRACIAS.

    ResponderEliminar
    Respuestas
    1. Bueno, basta con que copies y pegues formato (la brocha del portapapeles)...
      Saludos

      Eliminar
    2. YA LO HICE PERO ME COPIA SOLO EL COLOR Y SI MODIFICO EL NOMBRE EN LA CELDA TAMBIEN ME LO COPIA A LAS OTRAS CELDAS...
      PARA EXPLICARME MEJOR...
      TENGO LA FORMULA PARA QUE CUANDO ESCRIBA SERVICIO EN LA CELDA M4 RELLENE DE COLOR AMARILLO EL RANGO "A4:BL4", EL PROBLEMA ES CUANDO COPIO EL FORMATO, ME RELLENA LAS CELDAS COPIADAS DEL MISMO COLOR QUE TODO EL RANGO QUE ESTÁ EN LA FORMULA ES DECIR, AHORA FORMAN PARTE DEL RANGO A RELLENAR) Y AL MODIFICAR M4 TAMBIEN RELLENA TODAS LAS DEMAS...
      ME EXPLIQUÉ BIEN?
      GRACIAS.

      Eliminar
    3. Lo siento José Luís,
      no comprendo..
      si quieres tener el mismo formato condicional (con las mismas reglas), basta lo comentado...
      Si quieres otras reglas diferentes, tendrás que generar unas nuevas.

      ???

      Saludos

      P.D.: por favor, evita escribir en mayúsculas.. equivale a estar gritando.Gracias

      Eliminar
  40. ya intente lo que dices de copiar al porta papeles pero quedan como sin fueran parte del rango, al copiar el formato, no queda como la formula anterior,es decir, al modificar la celda "m4" se rellena todo el rango que esta en la formula (a4:bl4) pero también rellenan las celdas a las que copié la fórmula y no me respeta que ahora al modificar "m5" rellene solo el rango "a5:bl5" y así sucesivamente.

    ResponderEliminar
    Respuestas
    1. te has asegurado que la fórmula que introdujiste en el formato condicional era:

      =$M4="JORGE"

      es importante que la referencia relativa fije la columna y NO la fila (o ambas)...

      Eliminar
  41. una consulta, como hago si tengo un formulario que quiero q al presionar "GENERAR" me llene un formato que esta en otra hoja, extrayendo de la hoja 1 los datos de la base de datoss? xfa!

    gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      solo tendrías que asociar al evento Click de ese botón GENERAR de tu UserForm la acción de copiado y pegado de una hoja a otra.
      Puedes ver algunas formas de hacer este copiado en
      http://excelforo.blogspot.com.es/2011/02/vba-formas-de-copiar-rangos-o-celdas.html

      Saludos

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

    ResponderEliminar
    Respuestas
    1. Hola,
      lo siento, no termino de entender las premisas que planteas..
      si pudieras enviarme el fichero a
      excelforo@gmail.com
      le echaría un vistazo y quizá comprendiera mejor.

      Un saludo

      Eliminar
  43. muy útil su ayuda, señor Excelforo, podría ayudarme con un código en VBA para validar varias celdas, pero recorriendo con un mensaje en cada validación que haga en su recorrido, pienso que lo mejor podría ser con If-then, pero no veo encuentro la forma de integrar los if-then para cada validación. deje un ejemplo del código que quiero integrar:

    Sub ejemplo3()
    If Sheets("Captura2").Range("C6") = Empty And Range("C6").HasFormula = False Then
    MsgBox "La celda (C6) Número Documento Contable esta vacía, favor llenarla y ejecutar de nuevo"

    If Sheets("Captura2").Range("C9") = Empty And Range("C9").HasFormula = False Then
    MsgBox "La celda (C9) Valor de la Factura esta vacía, favor llenarla y ejecutar de nuevo"

    If Sheets("Captura2").Range("C15") = Empty And Range("C15").HasFormula = False Then
    MsgBox "La celda (C15) Número Documento de Amortización esta vacía, favor llenarla y ejecutar de nuevo"

    If Sheets("Captura2").Range("G9") = Empty And Range("G9").HasFormula = False Then
    MsgBox "La celda (G9) Número del Pedido del Anticipo esta vacía, favor llenarla y ejecutar de nuevo"

    If Sheets("Captura2").Range("G12") = Empty And Range("G12").HasFormula = False Then
    MsgBox "La celda (G12) Valor de la Amortización esta vacía, favor llenarla y ejecutar de nuevo"

    Else
    Call Captura_Datos2
    End If
    End If
    End If
    End If
    End If

    End Sub

    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Hugalex,
      si quieres incorporar diferentes If debes construirlos con la forma
      if .. then
      ....
      elseif ... then
      ...
      elseif ... then
      ...
      else
      ....
      end if

      un saludo

      Eliminar
  44. Gracias Ismael, lo organice con elseif y funciono, saludos, y gracias por todo.

    ResponderEliminar
  45. Buen día mi pregunta es la siguiente
    tengo una base de datos que alimento diariamente la cual puede llegar hacer hasta 50.000 registros, entonces, es una consolidado de informes diarios los cuales tiene un estado puede ser efectivo, devolución, o siniestro (columna K). al realizar el consolidado en la última columna (o) se encuentra el campo ruta imagen del producto la cual realice una formulación para crear el hipervínculo hacia la imagen de registro solo para los registros efectivos y siniestros, (para las devoluciones aparece la frase “sin imagen”), el cual desplazo la formula cada vez que consolido información, mi pregunta es hay algún método para que al solo copiar los campos de la información a consolidar (columnas de A hasta N) se rellene la última columna O (registro del consolidado con el hipervínculo o la frase ya sea el caso), otra además todos los registros de A1 hasta O1 tienen el nombre de cada campo

    ResponderEliminar
    Respuestas
    1. Hola,
      hay uno muy sencillo, convierte tu rango en Tabla (selecciona el rango que tengas en ese momento y presiona Ctrl+t).. la columna 'calculada' con la formulación de vínculo se autorrellenará hasta el último registro.

      Un saludo

      Eliminar
  46. Buenos días Ismael

    Tengo una lista en una hoja
    A 5
    B 3
    C 0
    D 1
    F 6
    G 0
    H 1

    y quiero en otra hoja rellenar unas celdas con los registro de la lista anterior que sean mayores que 0. ¿Puedes ayudarme?

    Salu2 y gracias

    ResponderEliminar
    Respuestas
    1. Hola Manuel,
      completa un filtro avanzado sobre la segunda columna, con el criterio >0, seleccionando la opción del filtro avanzado Copiar a.

      Un cordial saludo

      Eliminar
    2. Gracias Ismael, pero la idea es que se actualice la segunda hoja de forma automática conforme cambie los valores en la tabla principal.

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

    ResponderEliminar
  48. Me podrias indicar como se haría lo mismo del ejemplo pero comenzando de abajo hacia arriba ya que tengo estos datos y necesito que *RUBRO se copie en otra columna al lados de las monedas


    USD 1
    EUR 2
    YEN 3
    *RUBRO1
    EUR 3
    YEN 5
    *RUBRO2

    ResponderEliminar
    Respuestas
    1. Hola Ricardo, que tal?
      espero te encuentres bien.
      La idea será la misma, solo cambia la línea 16 por:
      Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C+1"

      Un cordial saludo

      Eliminar
    2. Impecable Ismael, te quedo muy agradecido. Le voy a comentar a mi hijo que me ayudo un tocayo suyo.
      Saludos desde Uruguay

      Eliminar
  49. Hola, antes que todo quisisera felicitar y agradecerte por compartir tan util informacion. Tengo un textbox para ingresar datos a una hoja excel, el ancho de la celda de destino es aprox. de 1000 pixeles pero muchas veces la informacion ingresada sobrepasa este valor. Para estos casos me gustaria saber como distribuir el texto de tal manera que una vez completada la celda de destino, siga escribiendo en la celda imediatamente inferior. Espero me pueda ayudar.
    Saludos!

    ResponderEliminar
    Respuestas
    1. Hola Denis,
      no sería más sencillo aplicar a la celda destino la propiedad de Ajustar texto y que cuando llegue al máximo de ese ancho de 1000 px salte de línea (dentro de la misma celda)??.

      Una alternativa sería buscar la correspondencia entre el ancho en pixeles y caracteres del ancho de la columna. Para este quizá te ayude lo siguiente:
      http://excelforo.blogspot.com.es/2014/09/unidades-de-medida-para-excel.html

      Espero te haya orientado.
      UN saludo

      Eliminar
  50. ¿Hola, que tal? Mira te cuento,

    No se si será algo parecido a lo que acabas de explicar, s no lo es te pido disculpas por anticipado

    tengo un formulario con 2 combobox (Una con columna autonumérica (Columna A) y otra con
    nombres (Columna B)

    Mi problema es el siguiente, tengo una función que recorre una columna a y lo compara con el
    valor de la combobox1,

    ahora bién me gustaría que cuando la combobox1 y la celda activa tuvieran el mismo valor fuera
    copiando en la misma fila (A partir de la columna C) donde se encuentra el numero los nombres
    de la columna B.

    Aquí te dejo la función en cuestión

    Sub añadirdatos ()

    Range("A2").Select

    Do Until IsEmpty(ActiveCell)

    If Combobox1.Text = ActiveCell.Text Then

    ' Copiar a partir de la columna C los nombres del cmbbx2 según el valor de cmbbx1

    ActiveCell.Offset(1, 0).Select

    Else

    ActiveCell.Offset(1, 0).Select

    End If

    Loop

    End Sub

    Y un dibujo (+ o -) del resultado

    | A || B || C || D || E | ....
    | 1 || Daniel || Daniel || Rodrigo || Álvaro |
    | 2 || Rodrigo || Maria || Álvaro || |
    | 3 || Maria || Daniel || Maria || |
    | 4 || Álvaro || Daniel || Maria || |

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      no, no parece se parezca en nada a lo planteado ;-)

      En todo caso, puedes explicar algo más claro el problema, no me queda claro qué se debe copiar y pegar donde.

      Slds

      Eliminar
  51. Sí, visto de nuevo es un tanto lioso, lo explicaré con imagenes

    http://s2.subirimagenes.com/imagen/previo/thump_9326903ej1.png

    En la primera imagen tengo en la columna A unos identificadores y en la columna B unos nombres.

    http://s2.subirimagenes.com/imagen/previo/thump_9326905ej2png.png

    En la segunda imagen al iniciar la macro se mostrará este formulario, donde la columna A y la columna B se cargarán en 2 combobox (Llamémoslas CmbNumeros y CmbNombres para evitar lios)

    Aquí viene el asunto, al pulsar el botón del formulario ("Agregar cliente"), se ejecutará la siguiente función

    Te la vuelvo a dejar aquí

    '-----------------------------------------------------------------------------------------------

    Sub añadirdatos ()

    Range("A2").Select

    Do Until IsEmpty(ActiveCell)

    If Combobox1.Text = ActiveCell.Text Then



    ActiveCell.Offset(1, 0).Select

    Else

    ActiveCell.Offset(1, 0).Select

    End If

    Loop

    End Sub

    '-----------------------------------------------------------------------------------------------

    Esta función recorrerá los datos de la columna A y buscará similitudes con el valor de la CmbNumeros donde se almacena los id.

    Lo que quisiera conseguir es que cuando se cumpla el if, seleccione la fila donde se encuentra el valor de la CmbNumeros e imprima a partir de la última fila vacía el nombre que haya seleccionado en la CmbNombres. Quedando algo así como la imagen de abajo:

    http://s2.subirimagenes.com/imagen/previo/thump_9326906ej3.png

    En el ejemplo, los nombres de la columna C en adelante son los nombres resultantes. Basícamente, lo que querría conseguir es que se imprimieran nombres de CmbNombres según el numero seleccionado en CmbNumeros.

    Ya que estoy con el ejemplo aprovecho para preguntar, saber si hay un comando o algo parecido para que al actualizar la CmbNombres restando los nombres ya introducidos

    Ejemplo: teniendo en cuenta la última imagen, en la cuando CmbNumeros vale 1 en la fila se han introducido:

    Daniel, Carlos, María, Alberto y roberto

    pues a partir de aquí me gustaría que la CmbNombres Mostrara solamente los nombres que no se han introducido en la fila

    CmbNombres = Miguel, Alejandro, Juan, Pedro y Fran

    Muchísimas gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Daniel,
      si es posible envíame el fichero a
      excelforo@gmail.com
      y verifico algunas cosas... se me escapa cómo (en base a qué regla) se generan esas listas de nombres...
      Saludos

      Eliminar
  52. Buen dia, solicito de su ayuda por favor. Tengo un archivo de excel que necesito importar, pero debo de tener casa celda con 35 caracteres, como puedo rellenar esos espacios en blanco para poder cumplir con los 35 caracteres. Gracias!

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes aplicar una función para 'completar' ese número de caracteres, por ejemplo, si el dato lo tuvieras en B1, en otra celda C1:
      =REPETIR(" ";35-LARGO(B1))&B1

      Espero te sirva.
      Saludos

      Eliminar
  53. Hola buen día Ismael

    Tengo una situación que esta muy conectada con el tema del foro, pero no se como conetarlo para hallar la solución.
    La tabla es la siguiente.

    Tabla 1
    __A_B
    1_1_REF1
    2_2_REF3
    3_3_REF4
    4_4_REF7

    La columna A le esta dando el consecutivo a los datos de la columna B(y el resto de columnas).
    Lo que debo hacer es cambiar el consecutivo de todas las lineas con tan solo cambiar un dato de la columna A.

    Ejemplo 1
    La REF4 se encuentra en en el Consecutivo 3, lo quiero cambiar al Consecutivo 2.

    El paso a paso partiendo de la tabla 1 seria el siguiente:

    1. En la celda A3 reemplazo el valor 3 por el valor 2.
    2. Las celda A2 debe Auto_incrementar su valor en 1.
    3. La tabla se organiza de menor a mayor segun los nuevos valores de la columna A

    Tabla 2
    __A_B
    1_1_REF1
    2_2_REF4
    3_3_REF3
    4_4_REF7

    Ejemplo 2
    La REF7 se encuentra en en el orden 4, lo quiero cambiar al orden 1.

    El paso a paso partiendo de la tabla 1 seria el siguiente:

    1. En la celda A4 reemplazo el valor 4 por el valor 1.
    2. Las celda A1 debe Auto_incrementar su valor en 1.
    2. Las celda A2 debe Auto_incrementar su valor en 1.
    2. Las celda A3 debe Auto_incrementar su valor en 1.
    3. La tabla se organiza de menor a mayor segun los nuevos valores de la columna A

    Tabla 2
    __A_B
    1_1_REF7
    2_2_REF1
    3_3_REF3
    4_4_REF4

    Espero puedas ayudarme!

    ResponderEliminar
    Respuestas
    1. Hola Iván,
      se podría intentar añadir una columna auxiliar donde apareciera los valore de ordenación nuevos, según el cambio realizado en uno de los elementos...
      habría que buscar la regla por la cual se produce el cambio..
      quizá te sirva este post:
      http://excelforo.blogspot.com.es/2009/10/la-funcion-jerarquia-la-ordenacion-de.html

      Luego bastaría aplicar una ordenación de la base de datos según el campo auxiliar recién calculado.

      otras opciones requerirían programación.

      Un saludo y espero te oriente

      Eliminar
  54. Hola Ismael, intento hacer una macro que copie un rango que inicia en la Celda B1 y continua en B2 etc. que el numero que esta en B1 lo copie a partir de A1 un numero n de veces numero que lo pide al correr la macro, despues de copiar este numero que esta en B1 siga con el que esta en B2 y asi sucesivamente yo inicie con este codigo pero nose como hacer espero me ayudes y gracias de antemano aqui te dejo el codigo

    Sub COPIA_NUMERO()
    '
    Dim n As Integer
    Dim i As Integer
    Dim b As Range
    Dim a As Range


    Set b = Worksheets("Hoja1").Range("B1").End(xlDown)
    Set a = Worksheets("Hoja1").Range("A1").End(xlDown)
    numero = InputBox("Numero de Veces a repetir el numero")
    n = numero
    i = 1

    If b <> Empty Then

    For a = Empty
    b.Select
    Selection.Copy
    'Range("A" & i & ":A" & n).Select
    ActiveSheet.Range(Cells(1, i), Cells(n, 1)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    'Else
    ' i = n + 1
    ' End If

    End If


    End Sub

    ResponderEliminar
    Respuestas
    1. Hola,
      esto te podría servir:

      Sub repetir()
      y = 0
      numero = InputBox("Numero de Veces a repetir el numero")
      For Each celda In Range("B1:B3")
      x = 0
      Do While x < Val(numero)
      Range("A1").Offset(0 + y, 0).Value = celda.Value
      x = x + 1: y = y + 1
      Loop

      Next celda

      End Sub

      sólo ajuste el rango B1:B3 por el que tengas que recorrer.
      Saludos

      Eliminar
    2. Gracias me sirvió un montón
      Realmente te agradezco

      Eliminar
  55. Como darle color de relleno a una celda, ubicada en j8, dependiendo de las fechas que se digiten de e8i8?
    es decir, si tengo fecha en las dos primeras solamente(e8f8) aparezca color rojo, si también hay fechas en la (g8 y h8) aparezca la misma celda de color amarillo, y cuando aparezcan todas las celdas con fechas, la celda sea color verde.
    alguna macro!!!
    saludos

    ResponderEliminar
    Respuestas
    1. Hola Ximena,
      no parece una macro vaya a aportar mucho al asunto... bastaría aplicar un Formato condicional.
      Tres reglas condicionales con fórmula:
      1- color rojo: =CONTARA(E8:I8)=2
      2- color amarillo: =CONTARA(E8:I8)=4
      3- color verde: =CONTARA(E8:I8)=5

      todo esto con la celda J8 (a la que quieres dar color) seleccionada...

      Espero te sirva
      Saludos

      Eliminar
    2. si me sirvió! muchas gracias Ismael.
      slds.

      Eliminar
  56. tengo unos valores en el rango ("J12","J43") y quiero elegir uno de ello de manera aleatoria y colocarlo en la celda "A1"
    como puedo hacerlo por favor ayúdame
    gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes aplicar un condicional en la celda A1:
      =SI(ALEATORIO()>0,5;J12;J43)
      Así supeditas aleatoriamente el valor de A1 a la función ALEATORIO.
      Saludos

      Eliminar
    2. Gracias Ismael, pero lo que estoy necesitando es la una macro de VBA,te agradecería mucho si me pudieras ayudar o regalarme una pista no hay mucho sobre aleatorios.
      nuevamente gracias

      Eliminar
    3. Hola,
      te dejo algunos links con ejemplos de aleatorios.. con y sin macros.
      ejemplo1
      ejemplo2
      ejemplo3
      ejemplo4
      Saludos

      Eliminar
  57. Buenas tardes Ismael de antemano muchas gracias por este foro ya lo que estoy leyendo has apoyado a mucha gente, espero y me puedas apoyar ami tambien, sabes tengo una hoja de excel y necesito crear una macro que mediante el click a un boton me haga lo siguiente
    CONT ANT CONT ACT DIF

    54,898 63,292 8,394
    213,286 220,816 7,530
    197,100 203,297 6,197

    en esta tabla lo que necesito hacer en la celdas donde esta el contador final esa cantidad igual como esta se pase a las celada correspondiente de contador inicial y que en las celdas donde estan los contdores finales me queden en blanco o cero ya que aqui empiezo a teclear otra ves y cada mes los contadores finales y para hacer esto mas rapido necesito de una macro pero no se mucho de creaciones de macros me podrias ayudar? te lo agardezco bastante

    SAludos!!!

    ResponderEliminar
    Respuestas
    1. Hola alfonso,
      prueba con este sencillo código:
      Sub mover()
      Range("b2:B13").Copy Destination:=Range("a2")
      Range("b2:B13").Clear
      End Sub

      sólo cambia el rango B2:B13 por el rango que corresponda al contador final, y A2 por la celda donde empiece el contador inicial.

      Espero te sirva
      Un saludo

      Eliminar
  58. Hola me podrían ayudar por favro con un problema, necesito hacer una macro que de una lista de datos que están por fecha de día hábil poder insertar renglones con los días inhábiles (fines de semana y festivos) que no aparecen en la lista y que en las otras columnas se copien los datos del último día hábil.
    Ojalá puedan ayudarme.
    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      te recomendaría aplicaras un bucle, empezando desde abajo (desde la última fecha), y fuera verificando si existe un salto con la fecha anterior.. en caso afirmativo, insertaríamos una fila en ese 'espacio'...
      Para tomar el dato del último día hábil lanzaría otro proceso posterior similar al que se describe en este post.
      Saludos

      Eliminar
  59. Buen Dia; me podrian apoyar para lo siguinte por favor: Neceito una macro que me copie el encabezado en cada rango de datos.

    Gracias de Antemano

    Nombre edad nacionalidad direccion ocupacion Ingreso
    Juan Pedro 25 Mexicana Conocida Empleado 4500
    Juan Pedro 25 Mexicana Conocida Empleado 2000
    Juan Pedro 25 Mexicana Conocida Empleado 1000


    Ana Lucia 22 Mexicana Conocida Ama de Casa 1500
    Ana Lucia 22 Mexicana Conocida Ama de Casa 2500
    Ana Lucia 22 Mexicana Conocida Ama de Casa 800


    Patrica M 38 Mexicana Conocida Empleada 800
    Patrica M 38 Mexicana Conocida Empleada 600
    Patrica M 38 Mexicana Conocida Empleada 200

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      es complicado averiguar si existe un patrón para poder ir pegando el primer encabezado en los demás rangos...
      Quizá recorriendo cada celda de la primera columna y determinando una regla o dándole algún intervalo..
      for i=5 to 100 step 7
      ...
      next i

      y cuando verifique regla o intervalo aplicar alguna de las formas descritas aquí
      http://excelforo.blogspot.com.es/2011/02/vba-formas-de-copiar-rangos-o-celdas.html

      Espero te oriente o dé una idea.
      Saludos

      Eliminar
  60. Hola, necesito que me ayuden, tengo una macro para rellenar las celdas vacías por un guion pero lo que busco es poder ejecutar la macro solo si hay celdas vacías en la hoja que si no hay celdas vacías no lo haga. abajo sale la macro que estoy usando para rellenar con el guion.

    Sub RellenaCeldasenBlanco()


    Dim hoja As Worksheet
    Dim UltFila As Long
    Dim Rng As Range

    Set hoja = Sheets("DATOS CLIENTES")
    With hoja


    UltFila = .Range("A" & .Rows.Count).End(xlUp).Row

    Set Rng = .Range("A1:H" & UltFila)
    End With
    Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "-"

    Set hoja = Nothing
    Set Rng = Nothing

    End Sub


    saludos!!!

    ResponderEliminar
    Respuestas
    1. Hola Francisca,
      Para lanzar el proceso de forma condicionada podrías aplicar un IF..THEN con la función CountBlank, algo de este estilo..
      IF Application.WorksheetFunction.CountBlank(Range("C1:C10"))>0 THEN
      .....tu código
      ENDI IF

      Saludos

      Eliminar
    2. Muchas gracias :) , funcionó muy bien.

      Eliminar
  61. Gracias por el aporte pero tengo un problema he realizado macros de relleno pero no logro conseguir que esta se desplace de una columna a otro por ejemplo de A2:AB1000, ya que si en la primer columna estan llenas las celdas de datos ya no busca en las demás celdas de las próximas columnas, que le modificaras a tu macro??

    ResponderEliminar
    Respuestas
    1. Hola Elvin,
      en el caso expuesto es suficiente que selecciones el rango completo, sustituye las filas 7 a 13 de la macro por
      Set rng=Range("A2:AB1000")
      el resto quedaría como está.
      Saludos

      Eliminar
  62. Tengo un cuadro con celdas vacíasy necesito que las celdas vacías estén rellenas con la información que aparece en su mismo grupo: D4 y D5 con la información de D2, porque forman parte del grupo 2 (que es la primera columna) y así todo. No puedo rellenar celdas vacía con la información de la celda superior porque la Q6, Q7, Q8... que debería ir en blanco, irían con la información de Q3...
    Es que no sé subir imagen :(

    ResponderEliminar
    Respuestas
    1. Hola Laura,
      que tal estás?, un placer saludarte igualmente.

      para aplicar una macro o la funcionalidad expuesta en el link del primer párrafo, se necesita una regla o norma que sirva para todos los casos, en caso contrario es complicado (por no decir imposible), salvo hacerlo manualmente para cada caso particular de tu rango de celdas.

      No veo, según lo que expones, la norma que pueda servirte.. por lo que parece tendrás que hacerlo manualmente :(

      Saludos

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

    ResponderEliminar
  64. Hola Ismael, realmente muy buenos tus aportes de VBA.
    Necesito ayuda; tengo una fila de fechas seguidas, y quiero que al apretar un botón se agreguen mas fechas; por ejemplo tengo
    16-may 17-may y al apretar se sigan sumando filas con las fechas, lo mismo que se hace al correr la celda para que se autorellene, pero en una macro. Espero haberme expresado bien. Gracias!

    ResponderEliminar
    Respuestas
    1. Hola Maca,
      puedes construir esta macro

      Sub macro1()
      Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A" & Rows.Count).End(xlUp).Value + 1
      End Sub

      y luego añadirla a un botón (control de formulario).

      La macro supone las fechas están en la columna A...

      Saludos cordiales

      Eliminar
    2. Ariel, muchas gracias por la pronta respuesta.
      Me funcionó perfecto!
      Una última duda: en vez de que vaya agregando en la columna, agregue en la fila, que parte del código modifico? Pensé que en Offset(0,1), pero me falta algo más.
      Nuevamente muchas gracias!
      Saludos desde Arg.

      Eliminar
    3. Hola,
      prueba con
      Sub xx()
      Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(1, Columns.Count).End(xlToLeft).Value + 1
      End Sub

      Saludos
      P.D.: me llamo Ismael ;-)

      Eliminar
    4. Hola Ismael, muchísimas gracias! me sirvió, y perdón el error en tu nombre jaja.

      Eliminar
  65. Hola buenas noches! Excelente post! ojalá me pueda ayudar, tengo una tabla en excel y rellene algunas celdas de color verde (estas son salteadas), quiero saber si hay forma de rellenar las que están en blanco de otro color. Muchas gracias por su atención.

    ResponderEliminar
    Respuestas
    1. Buenos días y gracias!
      entiendo que las celdas de color verde también están vacías...
      Subiré al blog una posible solución al caso propuesto.
      Un saludo!

      Eliminar
    2. Muchas gracias!! en donde puedo checar. Y si las celdas verdes tambien estan vacias

      Eliminar
    3. Cesar,
      lo publicaré en breve..
      Si, la idea es que 'salte' las celdas verdes estén vacías.. si ya estuvieran con algún valor no tendría sentido rellenarla
      ;-)
      Slds

      Eliminar
    4. Vale Ismael, muchas gracias mi amigo. Saludos.

      Eliminar
  66. Hola, Ismael romero
    estoy haciendo una macro, donde quiero que se inserte una fórmula desde "Q1"
    hasta encontrar una celda vacía,
    la formulas es =SI(B1=B2,N1+N2+N3,0) y que sea progresiva
    osea que en la fila 1 sea /// =SI(B1=B2,N1+N2+N3,0)
    fila 2 sea /// =SI(B2=B3,N2+N3+N4,0)
    fila 1 sea /// =SI(B3=B4,N3+N4+N5,0) y así sucesivamente hasta encontrar la celda vacía, espero me puedas ayudar, intente algunas soluciones pero no me funcionan

    ResponderEliminar
    Respuestas
    1. Hola Arimel,
      necesitaría saber dónde está la celda vacía.. por que supongo que si insertas una fórmula en Q1:Q... será que esas celdas no tienen nada en ellas ???.
      En todo caso, si tienes claro dónde realizar la secuencia o loop que localice la celda vacía, la macro podría ser:
      sub xxx()
      for each celda in Range("rango a recorrer")
      if celda.value<>"" then
      range("Q"&celda.row).formulalocal="=SI(B1=B2,N1+N2+N3,0)"
      end if
      next celda
      end sub

      Ya te digo, habría que conocer mejor la situación de partida.
      Saludos

      Eliminar
  67. Hola, Ismael:

    Gracias por ayudar a las personas con poco conocimiento como yo, Lo que quiero hacer es que tengo una lista con filtro de varios nombres Alex, Juan, Pedro, etc, desordenados y un campo vacio a su costado que vendria a ser el nro de registro para cada nombre, ejemplo si lo filtro por Alex y me salen 10 registros estos se puedan numerar del 1 al 10 en el campo vacio, si filtro por Juan y me sale 8 registros estos se puedan numerar del 1 al 8 en el campo vacio y asi sucesivamente. Gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola Alex,
      supongamos tienes los nombres en el rango A2:A100, y en la columna B queremos poner la numeración, entonces en B2 insertas la fórmula:
      =CONTAR.SI($A$2:A2;A2)
      y arrastras la fórmula hasta el final.
      Saludos

      Eliminar
  68. Hola Ismael,
    Muchas gracias, me funciono el código que me diste, el único detalle es que me pega la misma formula en todas las filas y necesito que la formula sea progresiva...

    Sub xxx()
    For Each celda In Range("Q:Q")
    If celda.Value <> "" Then
    Range("Q" & celda.Row).FormulaLocal = "=SI(B1=B2,N1+N2+N3,0)"
    End If
    Next celda
    End Sub

    fila 1 inserte: "=SI(B1=B2,N1+N2+N3,0)"
    fila 2 inserte: "=SI(B2=B3,N2+N3+N4,0)"
    fila 3 inserte: "=SI(B3=B4,N3+N4+N5,0)"
    fila 4 inserte: "=SI(B4=B5,N4+N5+N6,0)"
    fila 5 inserte: "=SI(B5=B6,N5+N6+N7,0)"
    fila 6 inserte: "=SI(B6=B7,N6+N7+N8,0)" así sucesivamente hasta encontrar la celda vacía
    se puede hacer eso??



    ResponderEliminar
    Respuestas
    1. Ismael ya me quedo, me puse a investigar un poco, le cambie la .FormulaLocal
      por .FormulaR1C1 y me genera el resultado deseado muchas gracias por tu ayuda
      un saludo cordial

      Eliminar
  69. Gracias Ismael Me funcionó Eres un Capo gracias Maestro.
    Saludos Alex

    ResponderEliminar
  70. Hola
    ¡Felicidades por tu blog! es genial
    Me gustaría pedir tu ayuda estoy tratando de integrar una validación para que las filas vacias de la columna AA escriban "MT" en la columna AD, intenté esto pero no funcionó

    Sub AJDEFT()
    '
    ' AJDEFT Macro
    ' Acceso directo: CTRL+q
    '
    Range(Selection, Selection.End(xlDown)).Select
    Range("AA6").Select
    Selection.End(xlDown).Select
    Range("AD38").Select
    Range(Selection, Selection.End(xlUp)).Select
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(ActiveCell, Range("aa2")) Is Nothing Then
    If Intersect(ActiveCell, Range("ab2")) Is Nothing Then
    If Intersect(ActiveCell, Range("ac2")) Is Nothing Then Range("ad2").Value = "MT"
    End If
    End Sub
    Range("a1").Select
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      lo primero decirte que no puedes incluir un Sub dentro de otro como planteas..
      En todo caso no me queda claro si quieres lanzar un proceso una sola vez que verifique qué celdas están vacías para completarlas con el texto 'MT', o si quieres hacerlo automático, con un evento, en caso que ocurra algo...

      En ambos casos no haría falta emplear macros.
      Si quieres lanzar un proceso simplemente selecciona las celdas vacías (sigue los pasos Ir a Especial > Celdas en blanco y una vez seleccionado las celdas vacías escribe MT y presiona Ctrl+Enter (en vez de solo Enter).
      La otra forma ('automa´tica') sería con una fórmula SI escrita en AD...

      Saludos

      Eliminar
  71. Hola! Me gustaría que me ayudaras a resolver una duda con un ejercicio que me plantean( Visual Basic, excel). No se que función de visual basic usar para realizarlo, pero me dicen que use : for each. Se trata de un lista de horas que nos dan y nos dicen que en la columna siguiente mostremos la resta entre hora de la fila en que esta y la anterior( es un ejercicio de simulación). Por ahora se que para rellenarla se hace así:
    Sub punto4()
    For Each celda In Range("D8:D585")
    celda.Value = 0

    Next celda


    End Sub
    Pero necesito que el valor de la celda sea el valor de la celda de al lado menos la del valor de la de arriba de esa.
    Muchísimas Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba con:

      Sub punto4()
      For Each celda In Range("D8:D585")
      celda.offset(0,1).Value = celda.Value - celda.offset(-1,0).value
      Next celda
      End Sub

      saludos

      Eliminar
  72. Hola, un foro interesante,

    Mis conocimientos en Excel son básicos, pero desearía poder crear un registro, que en un libro de Excel contenga las entradas, es decir, donde ingresaría la información, y en un segundo libro se vaya ingresando automáticamente la información pero en un orden diferente, tipo Base de Datos, he logrado que la información se copie, pero al realizar la variación en el libro 1, tambien varia en el libro 2, la intención seria que se registre y que pase a otra celda para que se continúe el registro o un nuevo registro, espero haberme dejado entender, Gracias!, un foro bastante instructivo.

    Saludos

    Rogger

    ResponderEliminar
    Respuestas
    1. Hola Rogger,
      la idea te la puede dar lo explicado en esta otra entrada
      http://excelforo.blogspot.com.es/2014/04/vba-select-case-para-distribuir-datos.html

      Creo que la técnica puede ser válida para lo que necesitas, con mínimos ajustes.
      Saludos

      Eliminar
  73. Hola Ismael
    Muchas gracias por contestar mi pregunta, déjame explicarme el objetivo de la macro que quiero hacer es que un archivo txt se descomprima con un Text To Colums, luego se modifiquen los formatos de algunas columnas y finalmente que en las filas que tengan datos en la columna AD aparezca "MT" en la columna AJ, en realidad es muy fácil hacer todo esto pero por ser un proceso largo me gustaria automatizarlo con una macro
    si me lo permites me gustaria mucho enviarte mi lay out para que le eches un vistazo

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      pasa un loop por la columna AD
      for each celda in range("AD1:Ad100")
      aplicándole un condicional IF..THEN
      Algo de este estilo:
      for each celda in range("AD1:AD100")
      if celda.value="MT" then cells(celda.row,"AJ").value="MT"
      next celda

      Saludos

      Eliminar
  74. Hola, Ismael:

    Gracias por la ayuda anterior, quisiera hacerte nuevamente otra consulta, tengo un listado de 20 registros los cuales en la 2da columna (B1- B20) son codigos únicos.
    Lo que quiero hacer es que por cada código se puedan insertar 11 filas adicionales repitiendo los campos B1 - H1 para las 11 filas insertadas debajo del primer codigo y en la columna A1 se debe generar un correlativo del 1 al 12.
    y asi sucesivamente para el segundo código, se tomará este segundo codigo y se se hara lo mismo, insertar 11 filas adicionales debajo de este codigo y repitiendo los campos B1 - H1 y en la columna A1 se debe generar un correlativo del 1 al 12.

    Ejem data original.
    A B C D...........H
    20 alex 3550
    30 Jhon 4550
    40 Paul 6950

    Ejem terminada.
    A B C D...........H
    1 20 alex 3550
    2 20 alex 3550
    3 20 alex 3550
    4 20 alex 3550
    5 20 alex 3550
    6 20 alex 3550
    7 20 alex 3550
    8 20 alex 3550
    9 20 alex 3550
    10 20 alex 3550
    11 20 alex 3550
    12 20 alex 3550
    1 30 Jhon 4550
    2 30 Jhon 4550
    3 30 Jhon 4550
    4 30 Jhon 4550
    5 30 Jhon 4550
    6 30 Jhon 4550
    7 30 Jhon 4550
    8 30 Jhon 4550
    9 30 Jhon 4550
    10 30 Jhon 4550
    11 30 Jhon 4550
    12 30 Jhon 4550
    1 40 Paul 6950
    ......... igual qu las anteriores
    .........
    Gracias

    Saludos

    Alex Bazan

    ResponderEliminar
    Respuestas
    1. Hola Alex,
      habría diferentes formas... pero por aplicar lo explicado y relacionado en este post, podríamos hacer:

      Sub Macro1()
      For fila = 4 To 2 Step -1
      filas = fila + 1 & ":" & fila + 11
      Rows(filas).Insert Shift:=xlDown
      Next fila

      UltFila = Range("A" & Rows.Count).End(xlUp).Row + 11
      Set Rng1 = Range("A1:A" & UltFila)
      Rng1.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
      Set Rng2 = Range("B1:D" & UltFila)
      Rng2.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

      End Sub

      Saludos

      Eliminar
  75. Hola, Ismael:

    Gracias por la ayuda, disculpa por haberme explicado mal el código que me as mandado esta ok pero se esta incrementando en la columna B el ejemplo deberia ser así.

    data original columna A vacia
    --------------------------------------------
    A B C D...........H
    20 alex 3550
    30 Jhon 4550
    40 Paul 6950

    Data con macro columna A se llena del 1 al 12 los otros campos solo se repiten
    ------------------------------------------------------------------------------------------------------------------
    A B C D...........H
    1 20 alex 3550
    2 20 alex 3550
    3 20 alex 3550
    4 20 alex 3550
    5 20 alex 3550
    6 20 alex 3550
    7 20 alex 3550
    8 20 alex 3550
    9 20 alex 3550
    10 20 alex 3550
    11 20 alex 3550
    12 20 alex 3550
    1 30 Jhon 4550
    2 30 Jhon 4550
    3 30 Jhon 4550
    4 30 Jhon 4550
    5 30 Jhon 4550
    6 30 Jhon 4550
    7 30 Jhon 4550
    8 30 Jhon 4550
    9 30 Jhon 4550
    10 30 Jhon 4550
    11 30 Jhon 4550
    12 30 Jhon 4550

    Gracias

    Ismael

    ResponderEliminar
    Respuestas
    1. Hola,
      corrige el origen así:
      A B C D...........H
      1 20 alex 3550
      1 30 Jhon 4550
      1 40 Paul 6950
      saludos

      Eliminar
  76. Hola Ismael Romero,
    tengo una duda tengo una duda de una operación en ecxel
    te explico

    tengo en la hoja 2 una lista desde A1:A20 y
    en la hoja 1 en la celda A50 necesito el valor de A1
    en la celda A49 necesito el valor de A2
    en la celda A48 necesito el valor de A3

    existe alguna funcion para esto?
    ya que lo que he hecho es
    definir A50= hoja2A1 y arastrar hacia arriba pero como hacia arriba de A1 no hay valores me manda error, espero me haya podido explicar

    lo que quiero es poder arrastrar hacia arriba pero en positivo


    ResponderEliminar
    Respuestas
    1. Hola Arimael,
      en la celda A50 de la hoja1 introduce la siguiente fórmula, y luego arrastra hacia arriba:
      =INDIRECTO(DIRECCION(FILA($A$50)-FILAS($A$1:A49);1;;;"Hoja2"))

      Saludos

      Eliminar
  77. muchas gracias ya me funciono a la perfección, solo tengo una duda mas, en la ultima parte de la formula que dice "Hoja2" mi hoja que tiene los datos la renombre DB ya intente ponerle ese nombre sustituyéndolo en lugar de hoja pero no me funciona
    pero no me funciona

    Saludos

    ResponderEliminar
  78. Listo tenia un error en mi redacción muchas gracias, cabe mencionar que estoy muy interesado en tu curso en Enero estare aplicando para tu curso muchas gracia por tu ayuda
    Saludos y ecxelente dia

    ResponderEliminar
  79. Buenos dias
    Por favor me pueden colaborar con lo iguiente

    ResponderEliminar
  80. Buenos dias
    Me pueden colaborar con lo siguinete

    Estoy realizando una macro para que me realice la siguiententarea
    De una plantilla en la hoja 1 me copie unas celdas fijas y me las pegue en forma de lista en la hoja dos y que yo pueda decidir a partir de que celda empiece a pegar
    La idea es poderrealizar tabulaciones
    Por su colaboracion mil gracias

    ResponderEliminar
  81. Buenos días

    Ismael

    Muchas gracias por la información que me regalaste , aplique todas las formas pero no logre adaptarla a lo que pretendo

    este es el código que estoy utilizando

    pero quería saber si hay una forma mas simple

    Sub prueba1()

    a = 2

    While Sheets("Hoja2").Cells(a, 1) <> ""
    a = a + 1
    Wend
    Sheets("Hoja2").Cells(a, 1) = Sheets("Hoja1").Range("F1")
    Sheets("Hoja2").Cells(a, 2) = Sheets("Hoja1").Range("A1")
    Sheets("Hoja2").Cells(a, 3) = Sheets("Hoja1").Range("E15")
    Sheets("Hoja2").Cells(a, 4) = Sheets("Hoja1").Range("E16")
    Sheets("Hoja2").Cells(a, 5) = Sheets("Hoja1").Range("E17")
    Sheets("Hoja2").Cells(a, 6) = Sheets("Hoja1").Range("E18")
    Sheets("Hoja2").Cells(a, 7) = Sheets("Hoja1").Range("A3")
    Sheets("Hoja2").Cells(a, 8) = Sheets("Hoja1").Range("B3")
    Sheets("Hoja2").Cells(a, 9) = Sheets("Hoja1").Range("C3")
    Sheets("Hoja2").Cells(a, 10) = Sheets("Hoja1").Range("D3")
    Sheets("Hoja2").Cells(a, 11) = Sheets("Hoja1").Range("E3")
    Sheets("Hoja2").Cells(a, 12) = Sheets("Hoja1").Range("F3")
    Sheets("Hoja2").Cells(a, 13) = Sheets("Hoja1").Range("C9")
    Sheets("Hoja2").Cells(a, 14) = Sheets("Hoja1").Range("C15")
    Sheets("Hoja2").Cells(a, 15) = Sheets("Hoja1").Range("C18")
    Sheets("Hoja2").Cells(a, 16) = Sheets("Hoja1").Range("C12")


    End Sub

    por tu colaboración mil gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      cuando el origen de la Hoja1 esta tan disperso (+/- 15 celdas separadas) no hay muchas más salidas para poder copiar y pegar...
      Saludos

      Eliminar
  82. ok

    Ismael

    mil gracias por tu tiempo

    Saludos

    ResponderEliminar