lunes, 25 de julio de 2011

VBA: Una utilidad de OFFSET en macros de Excel.

Hace pocos días vimos cómo, mediante una macro, podíamos evaluar coincidencias entre diferentes rangos (ver). En el post de hoy, analizaremos una de las utilidades o finalidades que podemos dar en nuestras macros a Range.Offset(núm_filas;núm_columnas).
Supongamos el siguiente ejemplo en el que disponemos de una tabla con diferentes elementos (ALTO, MEDIO, BAJO o vacío), de los cuales necesitamos obtener cuáles son sus referencias:


Se trata de obtener un rango variable del elemento elegido para el filtro (ALTO, MEDIO, BAJO o vacío); por ejemplo, si seleccionamos el elemento 'ALTO' deberíamos obtener el rango con los siguientes valores: AA-A, AC-C y AE-E
ya que son estos precisamente las coordenadas en nuestra tabla para los tres elementos 'ALTO' existentes.

En este caso, nuestra macro evaluará celda por celda cada una de las pertenecientes a nuestro origen, y en caso de que el valor de dicha celda coincida con el elemento seleccionado lo colocará en un orden predeterminado, una debajo de otra (sin espacios).

Es precisamente este el fin que le daremos en esta ocasión a la función OFFSET

Debemos incluir nuestro código VBA en un módulo del Explorador de proyectos dentro del Editor de VBA (Alt+F11):

Sub MuestraCoincidencias()
'www.excelforo.blogspot.com
Dim CompareRange As Variant, x As Variant, y As Variant
Dim referencia As Variant
'definimos cuál es el valor a filtrar
'para nuestro ejemploel valor que tenga la celda I1
referencia = Range("I1").Value
'iniciamos un contador
contar = 0
'limpiamos todos los valores existentes previos.
Range("I2:I50").Clear
'mediante la instrucción FOR pasaremos por todas las celdas que correspondan
'a las filas de la 3 a la 10
'y a las columnas de la 2 a la 7 (B a G)
For i = 3 To 10
For j = 2 To 7
Set celda = Sheets("Filtro").Cells(i, j)
'comprobamos si el valor de la celda coindice con el buscado
'si es cierto nos llevamos sus coordenadas de tabla a la celda I2
'y con OFFSET añadimos cada nueva coincidencia en una fila por debajo
'incrementando el contador en uno cada coincidencia
If celda.Value = referencia Then
Range("I2").Offset(contar, 0) = Cells(celda.Row, 1).Value & "-" & Cells(2, celda.Column).Value
contar = contar + 1
Else
contar = contar
End If

Next j
Next i

End Sub


Ya sólo queda probarla, en la celda I1 disponemos un desplegable con los valores ALTO, MEDIO, BAJO o Vacío, y ejecutamos nuestra macro y obtenemos:

8 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Hola ExcelForo
    Por favor una pequeña ayuda, mira no puedo hacerlo correr la macro me sale un error me dice se ha producido el error '9' en tiempo de ejecución: Subíndice fuera del intervalo.
    Y le doy Depurar y me manda a la macro que me lo selecciona de color amarillo y dice Set celda = Sheets("Filtro").Cells(i, j)
    Lo he revisado todo y esta copiado todo bien, Uso excel 2010
    Muchas Gracias
    Graciela.

    ResponderEliminar
    Respuestas
    1. Hola Graciela,
      sólo por confirmar, ¿te has asegurado que la hoja con la que trabajas se llama 'Filtro'?...
      Si has situado el rango de datos en la misma posición dentro de la hoja de cálculo, los bucles deberían funcionar correctamente...
      Si no es este el fallo, puedes enviármelo a
      excelforo@gmail.com
      y lo reviso.
      Slds

      Eliminar
  3. Gracias excelForo,
    Por ayudarme en el pequeño error, tenias razón me faltaba el nombre a la hoja, solo tú me lo podías resolver eres un genio.
    Gracias de antemano
    Slds Graciela.

    ResponderEliminar
  4. Tengo una duda, necesito hacer una macro en excel de la siguiente manera:

    tengo unos datos de una variable x en la columna A

    necesito que al ingresar por ejemplo el numero 8 en la celda B1 me ingrese en la columna C los primeros 8 datos de la variable x.

    ResponderEliminar
    Respuestas
    1. Hola Mary,
      no necesitas una macro para esto, basta emplear la función DESREF,
      con el ejemplo que propones, con un rango en C1:C20 seleccionado (por ejemplo... pero dependerá de los datos que tengas) y C1 activa, escribes:
      =DESREF(A1;;;B1;1)
      y ejecutas matricialmente (presionando Ctrl+Mayusc+Enter en lugar de sólo Enter.
      Listo, cada vez que cambies en B1 el número, cambiará el número de elementos mostrados en la columna C.
      Slds

      Eliminar
  5. Tengo una duda... tengo un archivo que se actualiza con una macro que cree...pero necesito que una instrucción avance de columnas según el mes....explico mi base termina en la columnas U y necesito que cuando termine el mes se empiece a rellenar la columna V...quedo atenta

    ResponderEliminar

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