domingo, 16 de febrero de 2020

CurrentRegion o cómo encontrar primer fila libre.

Una de las tareas más habituales es tratar de encontrar la primer fila libre para agregar datos a una tabla u hoja de base de datos.
Y si bien algunos sugieren el uso de la propiedad 'CurrentRegion'... ¿es siempre la mejor opción? Definitivamente NO.
Observemos la siguiente imagen. Una tabla que se inicia en fila 7.
La información que podemos obtener con CurrentRegion es la siguiente:
Sub info()
rangox = [C15].CurrentRegion.Address
'total de filas de tabla. Se resta 1 si solo se requiere el total de filas de datos.
filas = [C12].CurrentRegion.Rows.Count           
columnas = [C12].CurrentRegion.Columns.Count
End Sub

Con respecto a encontrar la primer fila libre, no sirve tomar el total de 'filas' +1 salvo que la tabla inicie en fila 1.
En cambio en imagen anterior esto devolvería 5 lo que es incorrecto. También hay que sumar las filas no ocupadas por la tabla, es decir:
               filas = [C12].CurrentRegion.Rows.Count+7

En la siguiente imagen nuestra tabla inicia en fila 1. Contiene algunos datos ya ingresados  y deseamos agregar algunos más.
En la macro le indicamos que nuestra tabla inicia en fila 1, pero el mensaje nos dirá que la primer fila libre es 13.
Sub prueba2()
Set HojaDestino = ActiveSheet.Range("A1").CurrentRegion
NuevaFila = HojaDestino.Rows.Count + 1
MsgBox NuevaFila
End Sub
Es decir, la cantidad de filas ocupadas por la tabla + 1. Y esto se debe a que la tabla presenta una columna con fórmulas ya extendidas a futuro.

Por lo tanto, para encontrar la primer fila libre la mejor opción es recorrer la columna A (o la que fuese) desde abajo hacia arriba:
Sub prueba3()
NuevaFila = Range("A" & Rows.Count).End(xlUp).Row + 1
MsgBox NuevaFila
End Sub

Y si nuestra tabla presenta otra información más abajo, haremos la búsqueda a partir de esa segunda tabla hacia arriba. Aquí la col a considerar es B:
Sub prueba4()
NuevaFila = Range("B18").End(xlUp).Row + 1
MsgBox NuevaFila
End Sub

Quedaría aún otra opción para cuando se desconoce el inicio de la segunda tabla. Recorrer desde el título (en fila 3) hacia abajo hasta encontrar una celda vacía. También para cuando tenemos un modelo de 'Tabla' (las del menú Insertar).

Sub prueba5()
NuevaFila = Range("B3").End(xlDown).Row + 1
MsgBox NuevaFila
End Sub

NOTA: esta opción devolverá la última celda de la hoja (1048577) si la tabla es única y se encuentra vacía de datos. Y en el caso de ser una 'Tabla' vacía de datos devolverá la fila 21 para este modelo.

Descargar libro de ejemplo desde aquí.
Ver VIDEO 30 con otros ejemplos.





sábado, 1 de febrero de 2020

Métodos de Búsqueda en Excel, con VBA

Una tarea que siempre trae alguna dificultad es la de buscar datos en una hoja, ya sea para modificarlos, eliminarlos o solamente por consuslta.

Retomando entonces el tema de las búsquedas en Excel vamos a analizar, para una misma tabla de datos, los 3 métodos de búsqueda utilizados.

1- Utilizando la variable SET.
2- Devolviendo el resultado de la función BUSCARV.
3- Colocando en celda la función BUSCARV.

En entradas + videos anteriores analicé los errores frecuentes que se cometen por no contemplar la posibilidad de que el dato buscado no se encuentra en la tabla o base de datos.
(Ver videos  16 de Febrero 2019  y  25  de Junio 2019)

Ahora, veremos aquí claramente y en pocas instrucciones los 3 métodos a utilizar.

Contamos con una hoja de BASE, que en libro de ejemplo que dejo al pie se llama Hoja2.



Y en otra hoja tendremos las celdas con el dato a buscar y en columna siguiente esperaremos el resultado.



Allí se observan 3 botones que llaman a los 3 métodos. Se ejecuta habiendo seleccionado la celda que contiene el dato a buscar (col B)

Método 1:  Este método es el más apropiado cuando no queremos dejar fórmulas ni evaluarlas. Se complementa con el método FINDNEXT comentado en video  23  de Febrero 2019 y del que próximamente dejaré más ejemplos.
Siempre se debe evaluar la posibilidad de dato no encontrado.
  • Sub macro_set()     'buscar un dato en un rango
  • 'x Elsamatilde
  • 'busca el dato de la celda seleccionada en tabla Hoja2 col B
  • dato = ActiveCell.Value
  • 'se declara la hoja de la base
  • Set ho2 = Sheets("Hoja2")
  • 'se guarda en una variable el resultado de la búsqueda
  • Set busco = ho2.Range("B:H").Find(dato, LookIn:=xlValues, Lookat:=xlWhole)
  • 'si el dato no se encuentra devuelve un mensaje, sino el valor de la col G
  • If busco Is Nothing Then
  •     ActiveCell.Offset(0, 1) = "Dato no encontrado."
  • Else
  •     'se guarda en celda de col siguiente el valor de col G del dato encontrado
  •     ActiveCell.Offset(0, 1) = ho2.Range("G" & busco.Row)
  • End If
  • End Sub
NOTA: en caso de realizar la búsqueda en otro libro (que también estará abierto) se declarará la variable de búsqueda de este modo:
      Set variable = Workbooks(nombre del libro.extensión).Sheets(nombre de hoja)
     Ejemplo: Set ho2 = Workbooks("LibroConsultas.xlsm").Sheets("BASE")

Método 2: Se realiza un BUSCARV guardando en celda de col siguiente el resultado de esa función. Se debe tener presente de evaluar posible error de dato no encontrado mediante el uso del método ON ERROR y el objeto ERR.Number.
  • Sub macro_resulta()      'devuelve el resultado de la función BUSCARV
  • 'x Elsamatilde
  • 'busca el dato de la celda seleccionada en col B de la Hoja2
  • dato = ActiveCell.Value
  • Set ho2 = Sheets("Hoja2")
  • 'controla posible dato no encontrado
  • On Error Resume Next
  •     ActiveCell.Offset(0, 1) = Application.WorksheetFunction.VLookup(dato, ho2.Range("B:G"), 6, False)
  •     'si la función devolvió error se coloca un texto aclaratorio
  •     If Err.Number > 0 Then ActiveCell.Offset(0, 1) = "NO encontrado"
  • End Sub

Método 3: Se coloca en celda la fórmula con la función BUSCARV. Aquí si el dato no fue encontrado el resultado será #N/A tal como cuando escribimos la fórmula directamente en la celda.
  • Sub macro_formula()       'coloca fórmula con BUSCARV
  • 'x Elsamatilde
  • ActiveCell.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Hoja2!C2:C12,6,FALSE)"
  • End Sub

Para completar la fórmula con un control de error la instrucción sería:
  • ActiveCell.Offset(0, 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Hoja2!C2:C12,6,FALSE),0)"

Lo que en la celda se verá como:  =SI.ERROR(BUSCARV(B15;Hoja2!$B:$L;6;FALSO);0)

NOTA: para aprender a formular mediante VBA recomiendo el video 15  de Octubre 2018. 


Descargar ejemplo desde aquí.

VER VIDEO 29.