sábado, 5 de mayo de 2012

VBA: macro para obtener rango de celdas asociado a una selección.

Tiempo atrás expliqué como mediante el uso de un objeto Collection en una macro de Excel conseguíamos un listado de valores únicos de una forma muy sencilla (ver entrada).
Esta entrada nos servirá para responder la cuestión planteada, en la que se pide extraer los valores asociados a un dato previamente seleccionado; y veremos como podemos combinar diferentes Eventos sobre un mismo control(ActiveX) para conseguir diferentes efectos, en este ejemplo, usaremos el evento GotFocus y el evento Change.
Esta es la cuestión:

...La siguiente lista me muestra que en la columna A tengo un dato y en la columna B me muestra todos los valores asociados a la celda de la columna A. La pregunta es cómo puedo buscar, utilizando el dato de la columna A, los datos de asociados de la columna B...

Veamos la imagen de la tabla origen:

VBA: macro para obtener rango de celdas asociado a una selección.


Se puede observar en la imagen que en la columna A (campo 'Código') existen varios valores repetidos (ax, bx, cx, dx, etc.), y que asociados a estos, en la columna B (campo 'Descripción') les corresponden diferentes valores; para ax le toca ax-01, ax-02, ax-03 y ax-04; y así sucesivamente.
La idea es ser capaz de seleccionar un elemento único del campo 'Código' para de manera inmediata se despliegue en la Hoja de cálculo los valores asociados del campo 'Descripción'.

Para trabajar de manera más cómoda hemos convertido en Tabla el origen de datos, asignando además Nombres definidos a ambos campos:
Código =Hoja1!$A$2:$A$12
Descripción =Hoja1!$B$2:$B$12

El siguiente paso es sencillo, insertaremos desde la Ficha Programador > Controles > Insertar > Control ActiveX > Cuadro Combinado, es decir, insertaremos un control ComboBox en la hoja de cálculo, al que asignaremos para empezar un evento GotFocus, que habilitará el listado de valores únicos como elementos (items) del ComboBox al presionarlo.
Al hacer click sobre el Cuadro Combinado recien insertado se abre una ventana de código (en la Hoja1) en la que insertaremos lo siguiente:

Private Sub comboBox1_gotfocus()

Dim celda As Object
Dim i As Integer

ComboBox1.Clear
Set unicos = New Collection

For Each celda In Range("Código")
    On Error Resume Next
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

For i = 1 To unicos.Count
    ComboBox1.AddItem unicos(i)
Next i

End Sub


Con esto hemos conseguido rellenar el ComboBox con los elementos únicos que componen en campo 'Código', como vemos en la imagen:

VBA: macro para obtener rango de celdas asociado a una selección.


De momento el ComboBox no hace nada más, pero si añadimos a continuación un nuevo evento Change sobre él mismo, conseguiremos nuestro objetivo, que es listar los valores del campo 'Descripción' asociados a nuestra selección.
Asi que a continuación del código anterior escribimos:

Private Sub comboBox1_change()
Dim seleccionado As Variant
seleccionado = ComboBox1.Value
x = 0
Range(Range("E4"), Range("E4").End(xlDown)).Clear
Range("E4").Select
Dim celda2 As Object
For Each celda2 In Range("Código")
    If celda2.Value = seleccionado Then
    ActiveCell.Offset(x, 0).Value = celda2.Offset(0, 1)
    x = x + 1
    End If
Next celda2
End Sub


Con este segundo código, con el evento Change, indicamos que al seleccionar algún elemento del Cuadro combinado (ComboBox) liste, a partir de la celda E4 de la hoja las diferentes 'Descripciones' asociadas.
Podemos ver el resultado en la imagen:

VBA: macro para obtener rango de celdas asociado a una selección.

34 comentarios:

  1. Excelente aportacion Ismael!
    Ésta, permite tener dos combos dependientes, y hacerlo por VBA. Hay muchos ejemplos de dependientes, pero todos son directamente, no por VBA.
    En mi caso, me encuentro con un problema que tal vez me puedas hechar una mano o iluminar por donde ir.

    Por ahora unicamente he aplicado la función de rellenar el combo. Y me encuentro con ún error 1004, en el Rango("Codigo"). No lo encuentra.

    El Combo lo tengo en una hoja, el rango de celdas en otra hoja distinta. El codgo se ejecuta en la hoja que tiene el combo. No consigo que reconozca el nombre del rango. Las celdas tienen formato "General"... y por mas que he probado con dobles comillas, comillas simples, con un variable.. nada.
    Si tuvieses alguna idea al respecto te lo agradeceria.
    Un saludo,
    Oscar

    ResponderEliminar
    Respuestas
    1. Hola Oscar,
      gracias por tus palabras.

      Si estás empleando un nombre definido, yo empezaría por asegurarme que en su definición, el nombre tiene como ámbito todo el Libro (y no sólo la hoja en que esté).
      Luego, si el combobox lo tienes en una hoja diferente, quizás deberías (por asegurarte) añadir al código siempre la expresión
      Sheets("nombrehoja").Range(....
      así dirijes cada cosa a su sitio. No se me ocurre ahora mismo otra posibilidad.
      Slds

      Eliminar
  2. Hola Ismael,
    Pues fue la segunda opción, ya que el nombre estaba bien definido para todo el libro. Que facil!
    La verdad, es que no he tenido nunca al 100% claro el funcionamiento de estos redireccionamientos de rangos, porque en este caso esta definido para todo el libro, no?... pero bueno en fin!

    Muchas gracias, por la ayuda!
    Y a continuar el blog, que ayuda a mas de un visitante silencioso!
    Saludos!

    ResponderEliminar
    Respuestas
    1. Perfecto!!
      en general cuanto más completos seamos (yo el primero) dirijiendo expresiones mejor...
      incluso si trabajaramos con diferentes libros, serái conveniente empezar con WorkBook("xxx.xlsx").Sheets("hoja").range(...

      pero es más cómodo dejarlo en manos del Editor... aunque a veces pasan estas cosas.
      Slds

      Eliminar
  3. Muchas gracias por tus consejos y trucos....
    Me interesa una variación de este ejemplo, verás yo tengo un listado de articulos cada uno con su código, y luego cada articulo tiene unos cinco precios diferentes (columna 1= codigo; columna 2= descripcion; columna 3= precio 1; columna 4= precio 2.....), mi pregunta es qué modificación habria que hacerle al código del ComboBox del articulo para que me diese el listado de los precios al seleccionar el código de un producto?

    ResponderEliminar
    Respuestas
    1. Hola,
      no veo para que necesitas una macro para conseguir lo que quieres; ya que sería suficiente incluir una Celda validada, por ejemplo en H1, sobre el campo 'código'; y luego en I1, J1, etc introducir una fórmula BUSCARV, con valor buscado H1 y matriz la tabla con el listado de artículos.

      Pero si quieres aplicar la macro, por el motivo que sea, la modificación la tendrías que hacer en:
      If celda2.Value = seleccionado Then
      ActiveCell.Offset(x, 0).Value = celda2.Offset(0, 1)
      ActiveCell.Offset(x, 1).Value = celda2.Offset(0, 2)
      ActiveCell.Offset(x, 2).Value = celda2.Offset(0, 3)
      'etc
      x = x + 1
      End If

      Espero te sirva.
      Slds

      Eliminar
    2. Gracias por tu pronta respuesta...aunque he pensado algo que creo me seria mas util, espero me puedas ayudar porque aun soy un poco novato con el excel. Verás como te comenté tengo un listado de articulos con su código correspondiente y cada articulo tiene más de un precio. Qué solución me podrias dar para que cuando introduzca en una celda un código pueda obtener en otra celda un listado con sus diferentes precios para poder elegir uno de ellos, a modo de poder hacer una especie de factura, para que me entiendas. Gracias por todo de antemano, un saludo.

      Eliminar
    3. Hola,
      la solución pasaría por emplear validaciones de celdas, sobre rangos con Nombres definidos.
      Puedes ver un ejemplo parecido en
      http://excelforo.blogspot.com.es/2010/04/validacion-de-celdas-anidadas-y.html
      Slds

      Eliminar
    4. Bueno, la verdad es que me cuesta un poco seguir los conceptos del ejemplo, serias tan amable de hacer algún ejemplo que se parezca más a esto?...verás, como te comenté tengo un listado de articulos, cada uno con su código, y cada articulo con cinco precios diferentes, realicé una validación de celdas tanto de los códigos como de los articulos, y de los precios por columnas(aunque no sé si realmente me sirve asi), sería un estilo como esto...
      CODIGO ARTICULO PRECIO1 PRECIO2 PRECIO3 PRECIO4 PRECIO5
      1 LECHE 0.25 0.27 0.30 0.32 0.35
      2 .....
      Y lo que necesito, si es posible, es crear en una celda algún desplegable en el cual me dé la opción de elegir los diferentes precios de un producto cuando introduzca el código del mismo en otra celda. Tengo varias hojas, una con los articulos con su codigo y sus precios, otra para hacer las facturas (que es donde necesito esto), y otra para los clientes.
      Espero me puedas ayudar, te lo agradeceria enormemente. Un saludo y gracias de antemano.

      Eliminar
    5. Hola,
      enviame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
    6. Muchisimas gracias por todo Ismael, eres un máquina. Pronto tendrás más noticias mias (jeje), porque me gustaria hacer el libro un poco más completo, sobre todo para generar informes de clientes y productos o productos y lotes por fechas, etc...
      Lo dicho, mil gracias, saludos maestro.

      Eliminar
    7. Gracias a ti Victor,
      entiendo que la solución te ha servido...

      Era sencillo siguiendo las indicaciones
      ;-)

      Un cordial saludo

      Eliminar
  4. Hola Ismael,
    todo esta bien se entiende, pero la lista de valores unicos cargados no son ordenados, ya sea si fue un nuero en forma ascendente.
    como podria ordenarlos?
    en mi caso tengo un rango con años que van desde 2008 al 2013 son repetidos el cual me identifica el año de muerte y son valores repetidos.
    con tu macros me queda algo asi.
    2009
    2013
    2010
    2008
    2011

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola, hacerlo mediante macros es algo complejo, ya que requiere trabajar con Arrays y aplicarle un método de ordenación tipo burbuja
      http://excelforo.blogspot.com.es/2012/12/vba-algoritmo-de-ordenacion-tipo.html

      Te diría que lo más simple es que ordenes tu base de datos en ascendente por el campo 'Años'...

      Intentaré, en cuanto pueda, subir una entrada en el blog explicando cómo realizar esta ordenación.
      Slds cordiales

      Eliminar
  5. Que tal Ismael, mi duda es la siguiente, tengo un Formulario que estoy armando para cargar datos en una hora de excel, y en ese Formulario le agrego con Visual un Combo Box y quiero cargar ahi datos de una hoja de calculo. Agregando el Combo Box solo puedo hacerlo, pero en visual agregando como parte de un formulario me esta costando hacer referencia a un rango de celdas para que Visual lo cargue al Combo Box dentro de mi Formulario. Es para que al Usuario le sea facil, rapido y exacto la carga de por ejemplo un cliente.

    ResponderEliminar
    Respuestas
    1. Hola Ever,
      para cargar con datos un ComboBox (desde la hoja de cálculo) deberías emplear el método .AddItem, indicando que celda quieres utilizar, o bien la propiedad RowSource, que admite directamente la carga de un Rango de la hoja de cálculo...

      Un cordial saludo

      Eliminar
    2. Excelente y en el ojo como siempre, un abrazo Ismael, gracias x 1.000!!!!

      Eliminar
  6. Hola excelforo, una consulta como se puede hacer en el comboBox, cuando comienzo a escribe una letra me salta a la celda E4. Lo que quiero es que no me salte a la celda E4 que permanezca en el comboBox y me dé el resultado en la celda E4.
    muchas gracias
    Slds cordiales
    Carmen.

    ResponderEliminar
    Respuestas
    1. Hola Carmen,
      bastaría con una instrucción
      Range("E4").value=ComboBox1.Value

      Espero haberte comprendido bien.
      Saludos

      Eliminar
  7. Gracias por responderme excelforo, la fórmula me funciona asta ahí muy bien pero el resultado me da en diferentes celdas y no se borra el resultado anterior quiera saber porqué o estoy haciendo algo mal.
    un saludo
    Carmen

    ResponderEliminar
    Respuestas
    1. Perdona Carmen, pero ya me costó entender el primer comentario...
      ¿no se borrar el resultado anterior de donde???.. ¿quizá del ComboBox???
      si quieres borrar el contenido del ComboBox una vez lo hayas empleado, tendrías que emplear la instrucción
      ComboBox1.Clear

      Saludos

      Eliminar
  8. Sólo una consulta, estoy trabajando un formulario el cual tiene 3 combobox y un listbox, la idea es que estos combobox jalen información de un archivo consolidado donde tengo 70 proveedores distintos, 500 marcas de productos y 2500 subfamilias y 7000 artículos. Osea que cuando seleccione un proveedor en el siguiente combobox aparesca sólo las marcas que vende este proveedor, así si selecciono una de las marcas me de las subfamilias de la marca y del proveedor, la idea con el listbox es que a medida de que vaya seleccionando los datos dentro de los combobox la lista original de 7000 se vaya reduciendo hasta la cantidad mínima que cumpla con lo que digan los 3 Combobox.
    Espero pueda ayudarme, gracias :D

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      yo ejecutaría un evento Change en el último de los 3 combobox, para que al realizar la última selección cambie el ListBox.. aunque sería posible hacerlo para cada combobox, desarrollando las diferentes combinaciones posibles de tres elementos.
      El trabajo seguiría construyendo la carga del ListBox.. lo más sencillo sería un recorrido o bucle, del tipo FOR.. NEXT, que recorra la lista original de 7000 productos, aplicando un triple condicional
      IF ... AND ... AND.. THEN
      y cuando se cumplan cargar el registro al LisBox...

      Espero haberte orientado, es algo complejo contestar o responder a la petición de un desarrollo (ver Normas de uso del blog) por comentarios.

      Un saludo!

      Eliminar
  9. Cómo hago para intercambiar los valores que se encuentran en la primera y ultima filas de una columna seleccionada, mediante un botón?

    Cómo hago para intercambiar los valores que se encuentran en la primera y ultima filas de la primera y ultimas columnas de un rango seleccionado, mediante un botón?

    Cómo hago para intercambiar los valores que se encuentran en la primera y ultima filas de columnas múltiples seleccionadas, mediante un botón?

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      un gusto saludarte igualmente...

      para intercambiar valores tendrás que usar un procedimiento donde guardes como variables los datos a intercambiar, y una vez guardados poder realizar la reasignación en las celdsa nuevas correspondientes...

      Para determinar las celdas seleccionadas, dependiendo de su disposición puedes aprovecharte de propiedades como .currentregion, por ejemplo...
      Un cordial saludo

      Eliminar
  10. Buena tarde Ismael, tengo el siguiente código

    Sub copiar2()

    Dim rango As Range, x As Byte

    Set rango = Application.InputBox(Prompt:="Elige un rango", Type:=8) 'Set rango = objeto

    Set Destino = Application.InputBox("Celda Destino?", "Elija La Celda Destino", "", Type:=8)

    x = Destino.Row

    For Each celda In rango
    If celda <> "" Then Range(Chr(ActiveCell.Column + 64) & x) = celda: x = x + 1 'ActiveCell.Column =Número de la Columna activa
    Next
    End Sub

    el código copia un rango con un InputBox, y selecciona una celda destino con otro InputBox = Set Destino, la cuestión es que me copia las datos no donde selecciono la celda destino con el Set Destino sino que los traslada a la celda activa, sabes que pueda estar pasando? agradezco tu colaboración.

    ResponderEliminar
    Respuestas
    1. Hola,
      sería más simple indicar el destino de esta forma:
      Sub copiar2()

      Dim rango As Range, x As Byte

      Set rango = Application.InputBox(Prompt:="Elige un rango", Type:=8)

      Set destino = Application.InputBox("Celda Destino?", "Elija La Celda Destino", "", Type:=8)

      x = destino.Row

      For Each celda In rango
      If celda <> "" Then Cells(x, destino.Column) = celda
      x = x + 1
      Next
      End Sub

      Saludos

      Eliminar
  11. Gracias por responder Ismael, tu código funciona muy bien, pero la cuestión es que la practica del código inicial que te pase, copia los datos a el rango destino sin pegar las celdas en blanco (las omite), entonces no me serviría el tuyo, también detecte que el código inicial solo copia los datos dentro de una misma columna, pues cuando tomas varias columnas a copiar y las pego este me copia los datos diagonalmente en una misma columna; en resumen si copia dos columnas me debería copiar en dos columnas omitiendo las celdas en blanco. Gracias por tu colaboración.

    ResponderEliminar
    Respuestas
    1. Hola!
      en realidad el código comentado soluciona el problema que planteabas.. ya que el resto de condicionantes (no pega celdas vacías, pega todo en una sola columna, etc) es idéntico al código que exponías...
      Si quieres copiar y pegar un rango indeterminado (completo) sin condiciones te recomiendo leas este post:
      http://excelforo.blogspot.com.es/2011/02/vba-formas-de-copiar-rangos-o-celdas.html
      bastará un sencillo:

      rango.copy destination:= destino

      Saludos

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

    ResponderEliminar
  13. Hola Ismael, he estado buscando información en la web sobre como obtener los valores que se encuentren en un columna de un rango seleccionado, descubrí que puedo ponerle un nombre a un rango en tu blog, también vi que utilizas la "x" y les sumas 1,que iría saber si hay forma de aplicarlo para poder lograr lo que necesito

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Nicolay,
      no comprendí muy bien lo que necesitas... pero creo que necesitas buscar algo en un rango...
      si es así, te recomendaría usaras el método
      Range.Find

      Puedes ver un ejemplo aquí:
      https://excelforo.blogspot.com/2015/02/vba-trasladar-datos-de-inventario.html

      Espero te oriente
      Saludos

      Eliminar
  14. Buen día Ismael, intentare aplicar la función que me has dado, y otro que conseguí "Elegir", para intentar hacer el código.

    Lo que estoy intentado hacer es tomar un rango y enviarlo por correo a otras personas, debido a que necesito hacerlo muchas veces me toma mucho tiempo, por lo tanto decidí que con una macros me sería mucho más fácil.

    He publicado un ejemplo en esta página
    https://www.lawebdelprogramador.com/foros/Excel/1688081-Crear-macros-para-enviar-un-correo-con-un-rango-de-datos.html#i1688147

    Si no debo poner algún link que referencie a otra página me avisas para borrarlo

    Entonces mi problema yace al crear el asunto y cuerpo del mensaje, ya que quiero utilizar algunas celdas para hacer el asunto y otras para lo que deseo escribir.

    El código que llevo hecho no lo tengo a mano, pero sería algo como lo siguiente
    With Selection

    With ActiveSheet.MailEnvelope
    .Item.To = "aquí irá la persona a la que enviare el correo que extraerse de una base de datos"
    '.Item.cc = ""
    .Item.Subject = " OC "B1", proveedor "E1", revisar
    .Introduction = "está en la imagen que puse en la otra web"
    ' .Item.Send
    End With

    End With


    End Sub


    Si fuese un Rango en las mismas filas no tendría problema, pero al no seleccionar las mismas líneas, sino diferentes se me complica mucho ya que no se como hacer para tomar los datos de la columna seleccionada, así que decidí usar un With selection, si crees que sería mejor de otra forma sería de gran utilidad.

    ResponderEliminar
    Respuestas
    1. Hola,
      echa un vistazo a este otro post
      https://excelforo.blogspot.com/2017/12/vba-enviar-rango-destinatario-de-correo.html
      y lee algunos comentarios.
      En tu caso, según las imágenes de la otra web, yo tendería a ocultar las filas y columnas que no quieres enviar adjuntas,
      El mensaje en el cuerpo debe ir en el .Introduction, y en el .Item.Subject el asunto... en ambos casos puedes componer una cadena de texto concatenando celdas dispersas:
      "OC" & range("B1").value & " , proveedor "& range("E1").value & "revisar"

      Espero haberte entendido

      Saludos

      Eliminar

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