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:

VBA: Una utilidad de OFFSET en macros de Excel.


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:

VBA: Una utilidad de OFFSET en macros de Excel.

6 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