31 - Búsqueda desde una función personalizada.

Excel cuenta con varias funciones de búsqueda: BUSCAR en todas sus variantes o COINCIDIR por ejemplo.
Aquí  vamos a desarrollar otra función de usuario para que nos busque un dato en todas las hojas del libro y nos devuelva el resto de los campos del registro encontrado.
Sería algo como BUSCARV pero en todo el libro.

Para eso imaginé unas tablas mensuales, con información de distintos clientes, con un par de campos adicionales.

La búsqueda se realizará desde otra hoja donde colocaremos la función que desarrollamos.
Como toda función, la escribiremos con esta sintaxis:

= nombre_funcion(argumento1; argumento 2)  

Para el campo 'Pedido' será:

                                         =BUSCAR_DATO(K4;2) 

NOTA: el separador de funciones será el que tengan en su libro Excel, aquí utilicé punto y coma.

El código para esta función se colocará en un módulo del Editor y será el siguiente:

Function BUSCAR_DATO(dato, col)

i = 1

Do

    If Sheets(i).Name <> "PORTADA" And Sheets(i).Name <> "CONSULTA" Then

        x = Sheets(i).Range("B" & Rows.Count).End(xlUp).Row

        For Each cd In Sheets(i).Range("B2:B" & x)

            If cd.Value = dato Then

                resulta = cd.Offset(0, col)

                Exit For

            End If

        Next cd

    End If

    i = i + 1

Loop While resulta = "" And i <= Sheets.Count

If resulta = "" Then

    BUSCAR_DATO = "no ESTÁ"

Else

    BUSCAR_DATO = resulta

End If

End Function

Lo que se hace es recorrer el total de hojas del libro con el bucle DO...LOOP While,  desde el índice i = 1 hasta el total de hojas (Sheets.Count)

Se omiten las hojas PORTADA y CONSULTA ya que allí no se encuentran tablas de datos.

Luego se recorre la col B de cada hoja. Si el cliente coincide con el dato buscado se guardará en la variable 'resulta' el contenido de la col que se indica en el segundo argumento.

Si la variable 'resulta' queda vacía significa que no se encontró en ninguna hoja ese cliente y se devolverá un mensaje.

En el VIDEO 31 se comenta otro modo de armar esta función, dejando la parte de la búsqueda en una macro aparte. En el libro de ejemplo se encuentran los 2 módulos explicados. Pueden descargarlo desde aquí.

Para la búsqueda se pueden utilizar otros modos como el método FIND en lugar de recorrer la col B. Ese método se encuentra explicado en entradas anteriores (Junio 2019 y Febrero 2020) o en VIDEOS 25 y 29.

Acceso al  VIDEO N° 31.

30 - CurrentRegion o cómo encontrar primera fila libre.

Una de las tareas más habituales es tratar de encontrar la primera 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 primera 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 primera 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í.

Acceso al  VIDEO 30 con otros ejemplos.

29 - 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

'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

'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

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í.

Acceso al  VIDEO 29.

28 - Modificar-Eliminar registros de hoja BASE con Userform.

En entrada anterior, con fecha 14/01/2020, vimos un formulario para el registro de datos a una hoja.

Pero todo lo que se ingresa debe permitir ser modificado.... y eventualmente también eliminado.
Por eso, en esta entrada vamos a diseñar y programar un Userform para estas 2 tareas.

Vamos a trabajar con la hoja Base de Proveedores que utilizamos para el Registro de datos anterior a la que le agregamos una columna más para registrar la fecha de la modificación.
En caso de eliminar un registro podemos optar por 2 acciones: eliminarlo de la hoja base .... o quitarlo de la base y copiarlo en otra hoja que será como un historial de elementos eliminados.

El formulario tendrá un campo de búsqueda y mostrará los campos del registro encontrado. A continuación se solicitará confirmación, tanto para Modificar como para Eliminar.

Los códigos que requiere este formulario son los siguientes:

1- En el evento Initialize: 

    a- se guarda el nombre de la hoja base en una variable que se declara al inicio del módulo para ser utilizada en todas las subrutinas.

   b- se ordena la lista de nombres (en un rango auxiliar) para poder mostrarlos de modo ordenado. Esta macro la dejé en el mismo formulario. Por eso se la llama con la variable que indica cuál es la útima fila ocupada.

   c- se llena el control Combobox1 con la lista ordenada.

   d- el Combobox2 ya tiene su lista asignada en su propiedad RowSource (Hoja Listas)

Dim hop

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

Set hop = Sheets("PROVEEDORES")        

'se quitan posibles filtros en la hoja de Proveedores

With hop

    '.Unprotect

    If .FilterMode = True Then .ShowAllData

    .Select

End With

'se busca última fila del rango para llenar el combo

filp = hop.Range("B" & Rows.Count).End(xlUp).Row

If filp > 2 Then

    'si hay más de un registro se ordena la lista de nombres antes de mostrarla en el desplegable

    If filp > 3 Then Call ordenaLista(filp)

    For Each cd In hop.Range("K3:K" & filp)

        ComboBox1.AddItem cd.Value

    Next cd

End If

End Sub

Sub ordenaLista(fini)
'se limpia la col auxiliar K y se copia el rango actual de nombres
With hop
.Range("K:K").Clear
.Range("B3:B" & fini).Copy Destination:=[K3]
End With
ActiveWorkbook.Worksheets("PROVEEDORES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PROVEEDORES").Sort.SortFields.Add Key:=Range( _
"K3:K" & fini), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("PROVEEDORES").Sort
.SetRange Range("K3:K" & fini)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

2- En el evento Change del Combobox: se busca el registro y de encontrarlo mostrará en cada control el contenido de las columnas de esa fila. Se guarda la fila del elemento encontrado para sobreescribirlo o eliminarlo. Esta variable se declara el inicio del módulo con esta instrucción:

           Dim fily As Long

Private Sub ComboBox1_Change()

'busca la ubicación del registro

If ComboBox1 = "" Then Exit Sub

dato = ComboBox1.Value

Set busco = hop.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)

'si el dato se encuentra en la base se muestran todos sus campos

If Not busco Is Nothing Then

    fily = busco.Row

    Label7.Caption = hop.Range("A" & fily)

    TextBox4.Text = hop.Range("B" & fily)

    TextBox6.Text = hop.Range("C" & fily)
    ComboBox2.Text = hop.Range("D" & fily)

    TextBox7.Text = hop.Range("E" & fily)

    TextBox11.Text = hop.Range("F" & fily)

    TextBox9.Text = hop.Range("G" & fily)

    TextBox10.Text = hop.Range("H" & fily)

Else

    MsgBox "No se encuentra este Proveedor en la base.", , "ATENCIÓN"

    Exit Sub

End If

TextBox4.SetFocus

End Sub

3- El botón Modificar: sobrescribe en cada celda del registro el contenido de los controles. La fila está indicada en la variable guardada al momento de la búsqueda y declarada al inicio del módulo.

Se controla que haya un nombre (significando registro encontrado). 

Los campos Nombre, Teléfono, Saldo y Productos mantienen el formato del formulario de Registro, o sea mayúsculas, valores enteros, decimales y mayúsculas respectivamente.

Private Sub Aceptar_Click()

If TextBox4 = "" Then

    MsgBox "Falta el nombre del Proveedor.", , "Faltan Datos"

    TextBox4.SetFocus

    Exit Sub

End If

sino = MsgBox("¿Deseas guardar estos datos?", vbQuestion + vbYesNo, "Confirmar")

If sino <> vbYes Then Exit Sub

hop.Range("B" & fily) = TextBox4

hop.Range("C" & fily) = TextBox6
hop.Range("D" & fily) = ComboBox2

hop.Range("E" & fily) = Val(TextBox7)

If TextBox11 <> "" Then hop.Range("F" & fily) = CDbl(TextBox11)

hop.Range("G" & fily) = TextBox9

hop.Range("H" & fily) = TextBox10

hop.Range("I" & fily) = Date

'formato a las col

hop.Columns("G:G").AutoFit

If hop.Range("G1").ColumnWidth > 36 Then hop.Columns("G:G").ColumnWidth = 36

Application.ScreenUpdating = True

Call Cancelar_Click

'refrescar la pantalla

Application.ScreenUpdating = True

End Sub

4- El botón Cancelar: además de limpiar los controles vuelve a actualizar la lista de nombres para mostrarla de modo ordenada en el ComboBox.

Private Sub Cancelar_Click()

Label7.Caption = ""

'limpia todos los controles Textbox

For Each ct In Me.Controls

    If TypeName(ct) = "TextBox" Then ct.Value = ""

Next ct

fily = 0

'se limpia el combobox y se lo llena nuevamente con datos actualizados

ComboBox1.Clear

'se busca última fila del rango para llenar el combo

filp = hop.Range("B" & Rows.Count).End(xlUp).Row

If filp > 2 Then

    'si hay más de un registro se ordena la lista de nombres antes de mostrarla en el desplegable

    If filp > 3 Then Call ordenaLista(filp)

    For Each cd In hop.Range("K3:K" & filp)

        ComboBox1.AddItem cd.Value

    Next cd

End If
ComboBox2.ListIndex = -1

ComboBox1.ListIndex = -1

TextBox4.SetFocus

End Sub

5- El botón Eliminar: previamente a eliminar el registro de la hoja Base se lo copiará en otra hoja (Eliminados).

Private Sub Eliminar_Click()

If TextBox4 = "" Then Exit Sub

If ComboBox1 = "" Then

    MsgBox "Selecciona desde el desplegable el nombre del Proveedor a eliminar.", , "ATENCIÓN"

    ComboBox1.SetFocus

End If

sino = MsgBox("¿Desea eliminar este Proveedor?", vbQuestion + vbYesNo, "CONFIRMAR")

If sino = vbYes And fily > 0 Then

    'busca 1er fila destino y pega el rango. Luego coloca fecha de eliminación en col I

    With Sheets("ELIMINADOS")

        ini = .Range("A" & Rows.Count).End(xlUp).Row + 1

        hop.Range("A" & fily & ":I" & fily).Copy Destination:=.Range("A" & ini)

        .Range("J" & ini) = Date

    End With

    hop.Range("A" & fily).EntireRow.Delete xlUp     'se elimina de la base

End If

'se limpia el UF para iniciar una nueva búsqueda

Application.ScreenUpdating = True
Call Cancelar_Click

End Sub

6- El botón Salir: cierra el formulario. Al llamar al evento de cierre (QueryClose) recordar de volver a proteger la hoja si estuviese protegida. La clave es opcional. Además en este evento se limpia la columna auxiliar utilizada para ordenar la lista.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'se limpia la col auxiliar K

hop.Range("K:K").Clear

'hop.Protect        'opcional

End Sub

Descargar el ejemplo con su código desde aquí.

Acceso al  VIDEO 28.

27 - Crear registros en hoja BASE con Userform

Son muchas las ocasiones en que necesitamos crear registros e ir acumulándolos en una hoja tipo BASE, de modo lineal. Es decir 1 registro por fila con cantidad de columnas según los campos utilizados.

En esta entrada vamos a ver cómo se realiza un formulario de REGISTRO. En entradas posteriores hablaremos de las opciones de Modificar y Eliminar registros de esa base.

El siguiente modelo, que servirá para cualquier tipo de actividad (Clientes, Proveedores, Alumnos, Pacientes, etc) tendrá campos del tipo:
- Label: que permiten mostrar información que luego será guardada en una columna. En ese caso se trata de un código o número de Identificación del registro.
- Textbox: son los campos para introducir valores o textos.
- ComboBox: un desplegable para seleccionar algún elemento de una lista.
- CommandButton: los botones de acción: Aceptar, Cancelar y Salir.

A partir de este modelo podremos agregar más controles y más columnas en la hoja que actualmente presenta este orden:

Por ejemplo, podríamos agregar un control Label para la fecha del día y más controles TextBox y ComboBox para ingreso o selección de datos.

Al pie encontrarán el enlace para descargar el formulario con su programación completa.

¿Qué vamos a tener en cuenta al momento de programar un formulario de REGISTRO? 

1- Los LABEL los utilizamos para mostrar información que luego puede ser guardada en el registro o no. Será el campo de la numeración automática de registro. Es decir un control que no será modificado por el usuario.

     Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1

2- Los TEXTBOX pueden ser controlados al momento del ingreso. Por ejemplo un control de valores numéricos:

         Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
          If KeyAscii < 48 Or KeyAscii > 57 Then
             KeyAscii = 0
             MsgBox "Error en el dato. Solo se aceptan valores numéricos."
          End If
         End Sub

También pueden ser convertidos como el campo nombre pasándolo a mayúsculas a medida que se registra en él:

         Private Sub TextBox4_Change()
         TextBox4 = UCase(TextBox4)
         End Sub

3- Al inicio del módulo Userform se declara una variable para guardar el nombre de la hoja BASE.
         Dim hob

4- Desde el evento INITIALIZE del formulario se guarda en esa variable el nombre de la hoja de base. Esto servirá para mover la información a la hoja sin necesidad de activarla o seleccionarla previamente.
También en este evento de inicio se guarda en el Label el número del registro a crear.

         Private Sub UserForm_Initialize()
         Set hop = Sheets("PROVEEDORES")
         'otras instrucciones
         Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1
         End Sub

5- Al ACEPTAR se debe verificar que haya un dato clave, que en este caso es el nombre. Incluir en este control previo todos los campos que sean de caracter obligatorio. Y siempre es recomendable enviar un mensaje de confirmación.

         Private Sub Aceptar_Click()

         If TextBox4 = "" Then

              MsgBox "Falta el nombre del Proveedor.", , "Faltan Datos"

              TextBox4.SetFocus

              Exit Sub

         End If

         sino = MsgBox("¿Deseas guardar estos datos?", vbQuestion + vbYesNo, "Confirmar")

         If sino <> vbYes Then Exit Sub

6- Al realizar el pase a la hoja se tendrá en cuenta el tipo de dato. Los controles utilizados son del tipo 'texto' por lo tanto tendremos que convertirlos con las siguientes funciones:

   - VAL para guardar valores numéricos enteros.

   - CDBL  para guardar valores numéricos con decimales. Evaluar si el control se encuentra vacío.

'se pasa el registro. se busca la fila libre

fily = hop.Range("A" & Rows.Count).End(xlUp).Row + 1

hop.Range("A" & fily) = Label7.Caption

hop.Range("B" & fily) = TextBox4

hop.Range("C" & fily) = TextBox6

hop.Range("D" & fily) = ComboBox1.Text

hop.Range("E" & fily) = Val(TextBox7)

If TextBox11 <> "" Then hop.Range("F" & fily) = CDbl(TextBox11)

hop.Range("G" & fily) = TextBox9

hop.Range("H" & fily) = TextBox10

Como la columna de SALDO ya presenta un formato 'Moneda' con decimales y de color rojo para los negativos, que fue asignado desde la misma hoja para la columna completa no será necesario asignarle formato a la hora de guardar los campos.

7- Al finalizar el pase se limpiará el formulario para permitir un nuevo registro. Para esto se llamará a la misma macro que la del botón CANCELAR:

Call Cancelar_Click

End Sub

8- La macro para CANCELAR limpia los controles y actualiza nuevamente el número para el ID correlativo.

         Private Sub Cancelar_Click()
         'se limpian los textbox
         For Each ct In Me.Controls
             If TypeName(ct) = "TextBox" Then ct.Value = ""
         Next ct
         'se limpia el combobox
         ComboBox1.ListIndex = -1
         'se incrementa el ID del prov.
         Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1
         TextBox4.SetFocus
         End Sub

9- El botón SALIR cierra el Userform. Recordar que si se desprotegió (y quizás se mostró hoja oculta) habrá que volverla a proteger y ocultar. Esto se instruye en el evento de cierre:

         Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
         hop.Protect
         End Sub

Descargar el ejemplo con su código desde aquí

Acceso al VIDEO N° 27.