martes, 12 de febrero de 2019

El método FINDNEXT para realizar búsquedas contínuas.

Siguiendo con el tema iniciado en video 16 de mi canal (entrada actualizada en mes de Febrero 2019) para una búsqueda con el método FIND, vamos a tratar ahora de realizar una búsqueda continua, con más de un criterio.
Para ello haremos uso del método FINDNEXT.

Opción 1:
Partiremos de una tabla de varias columnas donde una de ellas será la del primer criterio (mes) y otra contendrá datos del segundo criterio (art.). La tarea será calcular el acumulado de registros que coincidan en MES y ART.

Si bien esto puede ser resuelto con un filtro y la función Subtotales, vamos a ver cómo sería la programación en VBA utilizando los métodos FIND para encontrar el primer elemento y FINDNEXT para continuar la búsqueda hasta cubrir toda la tabla.
En el Editor de macros insertaremos un botón (que lo asociaremos al botón 'Actualizar' de la hoja) y allí copiaremos el siguiente código.

     Sub busquedaRepetidos()
     'x Elsamatilde
     'busca el valor de F1 en todas las apariciones en hoja activa
     dato = Range("F1")
     'se inicia la búsqueda en col A de la hoja activa
     Set busco = Range("A:A").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
     'si no se encuentra ninguna coincidencia se notifica y finaliza el proceso
     If busco Is Nothing Then MsgBox "Dato no encontrado": Exit Sub
     'guarda la 1er fila encontrada
     filx = busco.Row
     'inicia un bucle
     Do
         'compara la col B con criterio en G1
         If busco.Offset(0, 1) = [G1] Then
             totx = totx + busco.Offset(0, 2)
         End If
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx
     'terminó la búsqueda. Se coloca el acumulado en H1
     [H1] = totx
     End Sub

Opción 2:
También podríamos utilizar método FINDNEXT para obtener el acumulado de todo enero, sin uso del segundo criterio. Lo único que se quitaría en este caso es la comparación con la celda G1.
La macro en la parte del bucle DO....LOOP WHILE nos quedará del siguiente modo:

     'inicia un bucle
     Do
         totx = totx + busco.Offset(0, 2)
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx


Descargar libro de ejemplo desde aquí.

VIDEO: https://youtu.be/BRQyf9lVccQ

Ver más macros con bucles en nuevo manual Bucles en Excel.

Búsquedas con VBA - Método FIND

Entre las funciones Excel quizás la estrella de todas es BUSCARV, la que nos permite buscar un dato y devolver el contenido de otra columna de la fila encontrada.

En VBA podría decir que la estrella es FIND , que nos permite buscar un dato y obtener su ubicación para luego movernos o devolver otros valores en relación a la referencia encontrada.

La instrucción con todos sus argumentos es:
Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What: dato a buscar
After: a partir de dónde se realiza la búsqueda
Lookin: búsqueda entre valores (xlValues) o fórmulas (xlFomulas)
LookAt: coincidir con el contenido total (xlWhole) o parcial (xlPart) de la celda.
SearchOrder: realizar la búsqueda por filas (xlByRows) o por columnas (xlByColumns)
SearchDirection: búsqueda siguiente (xlNext) o anterior (xlPrevious).

Pero como hay varias sintaxis posibles vamos a ver aquellas que pueden llegar a fallar (ejemplos 1 a 4) y aquellas más recomendables (ejemplos 5 a 7)

Ejemplo 1: sin control de error en caso de dato no encontrado
       
      Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Con Cells  le estamos indicando la hoja completa, no acotada a un rango en especial.
Con Activate o Select le estamos diciendo que cuando lo encuentre ya lo deje seleccionado.

Ejemplo 2:

       If j = 1 Then Set busca = ha.Find(numero)
      If j > 1 Then Set busca = ha.FindNext(busca)
      celda = busca.Address


Pero en cualquiera de los 2 casos, el código fallará si no encuentra el dato y por lo tanto no lo puede seleccionar o no puede obtener la dirección de la celda ya que no se encontró el dato a buscar.

Entonces habrá que colocar previamente una instrucción como la siguiente:
      On Error goto NoEncontrado
       Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Y una entrada donde se instruya qué hacer en caso de no encontrar el dato.
      NoEncontrado:
           MsgBox "El dato buscado no se encuentra en la hoja activa."
           Exit sub

NOTA: también es posible utilizar On Error Resume Next  pero esto hará que siga con el resto de las instrucciones lo que en algún momento dará un resultado equivocado.

Ejemplo 3: acotando el rango de búsqueda pero a partir de celda seleccionada
   
      Range("A1:H1").Find(What:="marzo", After:=ActiveCell, SearchOrder:=xlByRows).Select

La misma instrucción puede escribirse del siguiente modo. En este caso habrá que colocar una coma por cada argumento que se omite.
     Range("A1:H1").Find("marzo", ActiveCell, , xlByRows).Select

Aquí le indicamos el rango y con Select le instruímos que seleccione la celda encontrada.
Pero con After le estamos indicando que lo busque a partir de la celda activa.
Y esto nos dará error si la celda activa se encuentra a continuación del dato buscado o en otras filas.

Ejemplo 4: no indicando orden o dirección de búsqueda cuando el dato puede encontrarse en distintas celdas.
En la siguiente imagen se observa que el texto 'marzo' se encuentra en 2 lugares.

Para que no nos devuelva un dato erróneo debemos instruirle a Excel en qué dirección buscarlo con el argumento SearchOrder. En este ejemplo le indicamos que lo busque por columnas por lo que devolverá el de la celda A3:
        Cells.Find("marzo", , , SearchOrder:=xlByColumns).Select

Ejemplo 5: 
El método de búsqueda guarda los argumentos LookIn, LookAt y SearchOrder de la última búsqueda efectuada. Por lo tanto si no recordamos cuáles fueron esos argumentos utilizados debemos declararlos en nuestra instrucción para no correr riesgos de obtener resultados erróneos.
Una instrucción segura debiera incluir entonces:

     Range("A1:D8").Find("ma", Lookin:=xlvalues, lookAt:=xlPart, SearchOrder:=xlbyRows)



LookAt:=xlPart indica que el texto buscado debiera estar incluido en el contenido de la celda.
LookAt:=xlWhole indica que el texto debe coincidir con el contenido de la celda


Ejemplo 6: 
Al realizar búsquedas dentro de fórmulas, los argumentos que debemos incluir son:
LookIn:=xlFormulas
LookAt:=xlPart


Ejemplo 7: utilizando una variable declarada con SET
Este método puede ser el más preciso considerando la declaración de argumentos según cada caso.
Aquí buscamos en un rango de otra hoja, por columnas, un texto parcial y luego ejecutamos varias acciones con la celda encontrada. Si no se encuentra coincidencias se envía un mensaje.
     Sub buscando()
     Set ho2 = Sheets("Hoja2")
     Set busco = ho2.Range("A1:D8").Find("ma", LookIn:=xlValues, lookAt:=xlPart,     SearchOrder:=xlByColumns)
     If Not busco Is Nothing Then
         'podemos obtener todas las referencias del dato encontrado sin necesidad de seleccionarlo
         filx = busco.Row
         colx = busco.Column
        'traer un rango a partir de la celda encontrada
         rgo = Range(Cells(filx, colx), Cells(filx, colx + 2)).Address
         ho2.Range(rgo).Copy Destination:=ActiveCell
         'colorear celda encontrada
         busco.Interior.ColorIndex = 3
     Else
         MsgBox "No se encontró el dato buscado en la Hoja 2"
     End If
     End Sub

NOTA: Este método no requiere que se seleccione la hoja para obtener las referencias de la celda encontrada ni para realizar alguna tarea allí, como copiarla, darle color, obtener un rango a partir de la celda encontrada y tantas otras tareas.

Descargar ejemplo desde aquí.

ver VIDEO


sábado, 26 de enero de 2019

Minigráficos - Formatos con gráficos

Si necesitamos graficar rápidamente algún informe o ciertas columnas de una hoja de datos podemos recurrir a 2 herramientas que nos dejarán resultados en la misma columna (o en otra auxiliar). Ocupando menor espacio que un gráfico tradicional.

1 - Formatos con gráficos:
Entre los nuevos estilos de Formato Condicional podemos observar el que simula un gráfico de barras:
Se selecciona el rango que se desea graficar y se elige algún estilo de los mostrados en la opción 'Barras de datos'.
El resultado puede verse como en este ejemplo:
También podemos agregar otro formato más, como el de 'Escalas de color', quedándonos los 2 formatos superpuestos de este modo:

2- Minigráficos:
Esta herramienta requiere de una columna auxiliar donde colocaremos el minigráfico por cada fila de nuestro informe.
Seleccionamos la celda donde dejaremos el minigráfico y desde menú Insertar, Minigráfico se elegirá el modelo.

Se nos abrirá la siguiente ventana que nos permite seleccionar el rango de datos.

Si hemos seleccionado una sola celda para el gráfico, debemos seleccionar solo una fila de datos. Luego arrastraremos el resultado al resto de las celdas que necesitemos mostrar un gráfico.

Otro modo sería seleccionar todas las celdas de resultado (aquellas donde vamos a dejar el gráfico) antes de llamar a la herramienta. Y desde la ventana seleccionar todas las filas de datos.

En imagen siguiente vemos el resultado de 2 modelos de minigráficos: columnas y líneas.
Para borrar el gráfico de alguna celda seleccionarla con clic derecho, optar por Minigráfico, Borrar.

Para modificar el aspecto de los gráficos seleccionar una celda y desde el menú Herramientas de Minigráficos, Diseño se nos presentarán varias opciones: ajuste de color, puntos más altos o más bajos, etc.


VER VIDEO.

domingo, 13 de enero de 2019

Cómo crear Funciones personales

Si bien Excel presenta gran cantidad de funciones y en cada versión se van agregando otras nuevas, en ocasiones no encontramos la que nos hace falta para ciertos cálculos especiales.
En esos casos, generalmente recurrimos a una macro. Pero si necesitamos realizar ese cálculo de modo frecuente.... ¿porqué no desarrollar nuestra propia función?

En este video se puede seguir el paso a paso de cómo se crearon 2 funciones personales:
1- Encontrar un número en una matriz:
    =BUSCARM(nro; rango)


2- Sumar de modo alterno en un rango de una columna.
     = SUMA_ALTERNA(rango)

Las funciones se desarrollan en un módulo del Editor.
IMPORTANTE: las funciones personales deben ser desarrolladas para ser utilizadas en cualquier escenario. Verificar si el resultado obtenido coincide con los resultados esperados en los distintos escenarios.

A continuación los códigos para cada función que encontrarán en libro que se deja para descargar.

      Function BUSCARM(dato, rango)
      'buscar en la matriz
      For Each cd In Range(rango.Address)
          If cd.Value = dato Then BUSCARM = cd.Address(False, False)
      Next cd
      End Function

      Function SUMA_ALTERNA(rango)
      '1er fila y col del rango
      fil = Range(rango.Address).Row
      col = Range(rango.Address).Column
      'total de filas
      filas = Range(rango.Address).Rows.Count
      'se recorre el rango en saltos de 2
      For i = fil To filas + fil Step 2
          tot = tot + Cells(i, col)
       Next i    
     SUMA_ALTERNA = tot
      End Function


3- Sumar de modo alterno en un rango de una columna, con criterio.
Si además necesitamos sumar según alguna condición al estilo SUMAR.SI, agregaremos como segundo argumento el criterio deseado:
     = SUMA_ALTERO(rango;criterio)
Lo que cambiará es la instrucción de suma, incuyendo la condición. En este ejemplo se sumarán los conceptos que incluyan el texto 'Adaptador')

      Function SUMA_ALTERO(rango, criterio)
      'instrucciones
      For i = fil To filas + fil Step 2
          If InStr(1, Cells(i, col - 1), criterio) > 0 Then tot = tot + Cells(i, col)
      Next i
      'resto de instrucciones
      End Sub

Descargar libro de ejemplo
Ver VIDEO

martes, 18 de diciembre de 2018

Gifs animados en un formulario

En épocas festivas como es el mes de diciembre, podemos 'decorar' nuestros formularios con un detalle como es la incorporación de algún gif animado.

En libro que se deja de ejemplo (al pie queda el enlace), se partió del Userform de la entrada publicada en el mes de marzo 2018: Interactuando con Userforms.
Lo que se ha hecho aquí es correr hacia abajo el frame o marco del formulario para insertar una especie de banner con un gif animado.

Previamente habrá que verificar si se tiene instalado el control llamado Microsoft Web Browser. Para ello desde la ficha Desarrollador o Programador se buscarán entre los controles tal como se muestra en la imagen.

Al Aceptar dibujar en la hoja el control. Si se encuentra correctamente registrado aparecerá un cuadro negro (ver imagen), de lo contrario se nos mostrará un mensaje de que falta instalar ese control.
En caso de tener que instalarlo, se pueden seguir las instrucciones dejadas en el siguiente video,

Una vez ya verificado que se dispone del control correctamente instalado, procederemos a entrar al Editor de macros y trabajar con nuestro Userform.
Dependiendo del tamaño de nuestro gif extenderemos el formulario hacia abajo o a derecha y moveremos el frame para poder ubicar el gif.
Incluso podríamos cambiar de lugar algunos controles para dejar el gif dentro del frame o marco:
Ahora nos resta incluir el código para mostrar la animación. 
Las instrucciones las colocaremos en el evento Initialize ajustando nombre del archivo gif.

Private Sub UserForm_Initialize()
'web browser
  ruta = ThisWorkbook.Path         'la imagen se encuentra en el mismo directorio del libro
  archivo = "feliz-navidad"
  ext = ".gif"    
  
  WebBrowser1.Navigate _
  "about:<html><body scroll='no'><img src='" & _
  ruta & "\" & archivo & ext & " '></img></body></html>"

End Sub


Descargar ejemplo desde aquí.

Ver VIDEO.



martes, 4 de diciembre de 2018

Combinar estructuras o bucles.

En programación contamos con algunas estructuras de código. Es decir un bloque de instrucciones que puede utilizarse para varias tareas.
Cuando estas instrucciones son repetitivas las llamamos Bucles.
Así encontramos diferentes bucles que pueden ser:

  • While......Wend
  • Do (While)...... Loop  y su variante: Do.....Loop While
  • Do (Until)........Loop  y su variante: Do..... Loop Until
  • For   ...Next
  • For Each .... Next

Luego encontramos otras estructuras, que si bien no son repetitivas presentan un bloque de instrucciones comunes a varios escenarios:

  • If .....Else......End If
  • Select Case.... End Select
  • With .....End With

Estas estructuras pueden estar contenidas unas dentro de otras.
Para el ejemplo, imaginemos que tenemos un libro con gran cantidad de hojas cada una con su formato: rangos de datos que inician y finalizan en diferentes filas y columnas, hojas en las que se borra un rango completo y otras donde deben ser mantenidas las fórmulas, diferentes condiciones a la hora de protegerlas... y así cada hoja tendrá sus propias características.

Si trabajamos en un libro durante el diseño y programación agregando registros de prueba, en algún momento necesitaremos dejarlo limpio de datos para iniciar su uso real.
O quizás al finalizar por ejemplo un año contable, se necesitan limpiar algunas hojas de registros para iniciar un nuevo período.
Entonces utilizaremos varias de estas estructuras para luego incorporar las instrucciones de borrado de datos.
A continuación se puede observar el armado de la macro con el uso de las siguientes estructuras:

  1. For Each......Next
  2. Select Case ..... End Select
  3. With ......End With

      Sub LIMPIAR()
      'se recorren todas las hojas que deben ser vaciadas de datos:
      For Each sh In Sheets
          'se evalúa cuál es la hoja
          Select Case sh.Name
            'según el nombre de la hoja serán las instrucciones de borrado
            Case Is = "PRODUCTOS"
                 With Sheets("PRODUCTOS")
                    'instrucciones para hoja Productos
                 End With
           Case Is = "MOVTOS"
                With Sheets("MOVTOS")
                   'instrucciones para hoja Movimientos
                End With
           'el resto de las hojas
         End Select
     Next sh
     End Sub

En el siguiente VIDEO se explica en detalle este tema.

domingo, 25 de noviembre de 2018

Formato condicional con fórmulas

El Formato Condicional es una herramienta muy útil a la hora de marcar registros que cumplen ciertas condiciones.
Generalmente marcamos de color registros de una lista que presentan ciertos valores (textos o números).
Así por ejemplo, podemos marcar en una lista de días los domingos de algún color como se observa en la siguiente imagen:
Lo que se hizo fue seleccionar el rango. Y desde menú Inicio, Formato Condicional, Nueva regla se seleccionó la 2da opción. En los campos de criterio se puede elegir que sean 'igual', 'menor o mayor que' y otros más. Luego se completa el tipo de formato y ya lo tendremos listo.
En la siguiente imagen se optó por valores superiores a un número y así se observa:
Otra opción que nos presenta esta herramienta de formato, es aplicarlo a valores duplicados o únicos. En ejemplo siguiente se optó por marcar los duplicados. Previamente se seleccionó el rango y se aplicó el formato de color. 

Ahora, en este ejemplo vemos que se seleccionan los 2 duplicados. ¿Cómo debiéramos aplicar el formato si solo deseamos marcar la 2da o siguiente aparición de cierto valor, pero no el primero?.
Utilizaremos la última opción que nos ofrece el Formato Condicional: con fórmulas.

Ejemplo 1: marcar repeticiones.
La fórmula utilizada es CONTAR.SI(rango hasta la celda utilizada;valor ingresado) > 1
Es importante aquí respetar los signos $ ya que estos indican el mismo inicio de rango para todos los registros y hasta la fila del registro que se está ingresando.

Ejemplo 2: marcar el valor máximo.
Aquí utilizamos la función MAX en la siguiente fórmula:
=I3=MAX($I$3:$I$40)
Lo que estamos indicando en cada fila es que si la celda es igual al valor máximo del rango se aplique el formato. Nuevamente es importante respetar los signos $ .

Ejemplo 3: marcar el valor máximo si valor <> ""
A la fórmula anterior le agregamos otra condición par que no se pinte el rango cuando está vacío. La fórmula en este caso será:
=Y(I3=MAX($I$3:$I$40);I3<>"")

Nota: En Manual Excel se podrán encontrar otros ejemplos del uso de fórmulas en el formato condicional.

*Descargar ejemplo desde aquí.

*VER VIDEO.