36 - Filtros dinámicos con VBA, utilizando Userform

En el video anterior (N° 34) vimos cómo realizar una búsqueda dinámica directamente desde la hoja, utilizando Filtros Avanzados.

Hoy vamos a seguir con el tema, pero en esta ocasión utilizando un formulario o Userform.

En esta entrega encontrarán 2 ejemplos y en ambos el resultado de la búsqueda se muestra en un control ListBox.

Ejemplo 1: Desde un control TextBox se ingresará el texto a buscar en TODA la hoja, no importando en qué columna se encuentre. El resultado se irá mostrando en el control Lista.  La búsqueda se realiza de modo parcial, es decir que las celdas deben contener el texto buscado.  

Además este modelo presenta un control Combobox para realizar búsqueda por Nombre completo. A medida que se van registrando las letras se filtrará la lista.

En este formulario programamos 3 eventos:  Initialize (o sea al iniciar el formulario), TextBox_Change y ComboBox_Change en el caso de que además del control TextBox se cuenta con un desplegable también.

Ejemplo 2: En este modelo se utilizarán tantos controles TextBox como criterios tengamos para la búsqueda. Cada control corresponderá a una columna de la tabla. El resultado se irá mostrando en el control Listbox de modo filtrado. Los criterios se solicitan como: contenido en celdas de col A y C y coincidencia total con la columna B. 

Tanto en la apertura del formulario (evento Initialize) como desde el botón 'Limpiar' (CommandButton1_Click), se mostrará la lista completa en el control ListBox. 

El resto del código será para los 3 controles TextBox (evento Change) o la cantidad que tenga el formulario. Cada uno de estos eventos, luego de filtrar el rango correspondiente según su columna, llama a una macro común para el llenado del ListBox.

Los criterios para las columnas A y C se buscan de modo parcial, como parte del contenido de las celdas. En cambio para la columna B la coincidencia debe ser total.

Por ejemplo, para el primer TextBox:

Private Sub TextBox11_Change()
If TextBox11 <> "" Then

dato1 = "*" & TextBox11.Value & "*"         

Range("$A$3:$G$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=dato1, Operator:=xlAnd

Else

ActiveSheet.Range("$A$3:$G$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1

End If
Call llenalista
End Sub

Sub llenalista()
ListBox1.Clear
x = Range("A" & Rows.Count).End(xlUp).Row
If x < 4 Then Exit Sub
For Each cd In Range("A4:A" & x).SpecialCells(xlCellTypeVisible)

ListBox1.AddItem cd.Value

i = ListBox1.ListCount - 1

ListBox1.List(i, 1) = Range("B" & cd.Row)

ListBox1.List(i, 2) = Range("C" & cd.Row)

Next cd
End Sub

 

Acceso al VIDEO 36.

Desde el siguiente enlace se puede descargar el libro con la programación de los ejemplos, tanto del video 35 como del 36:

http://aplicaexcel.com/Blog/FiltroAvanzado.xlsm

 

 

35 - Filtros Avanzados con VBA

 Generalmente utilizamos un formulario o Userform destinado a búsquedas de información.

Pero si buscamos algo sencillo estos 2 ejemplos nos servirán: filtrar tablas de datos dinámicamente, es decir que a medida que escribimos los criterios o los seleccionamos ya vamos obteniendo el filtrado de los datos.

Ejemplo 1:  A medida que se escriben los criterios en un rango destinado a tal fin, la tabla se irá filtrando.

El proceso se ejecuta en el evento Change de la hoja donde se encuentra esta tabla. Y llama a una subrutina llamada 'filtrando' que se encuentra en un módulo.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'se controla lo ingresado en rango de criterios
      If Intersect(Target, Range("$A$2:$C$2")) Is Nothing Then Exit Sub
      On Error Resume Next
      Call filtrando
      End Sub

      Sub filtrando()

      'establecer fin de rango.

      filas = [A6].CurrentRegion.Rows.Count + 3

      'se incrementa en 3 por ser la cantidad de filas por encima del rango

      Range("A6:G" & filas).AdvancedFilter Action:=xlFilterInPlace, _

          CriteriaRange:=Range("A1:C2")

      End Sub

Para quitar el filtrado se borran las celdas de criterio (A2:C2)

Ejemplo 2: Se colocan las celdas de criterios en un rango auxiliar. Se ejecutará a medida que se vayan seleccionando celdas con los criterios elegidos de cada columna. La macro volcará el valor de la selección a las celdas de criterios del Filtro Avanzado para luego ejecutar el filtrado.

En este ejemplo, el proceso se ejecuta al seleccionar una celda de las columnas A:C. Por lo tanto el código se colocará en la hoja a trabajar, evento Selection_Change.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'solo se ejecuta cuando se seleccione alguna celda en columnas A:C, a partir de la fila 4
      If Target.Column > 3 Or Target.Row < 4 Then Exit Sub
      'según en qué columna se hizo la selección se volcará ese contenido al rango de criterios
      If Target.Column = 1 Then [J2] = Selection.Value
      If Target.Column = 2 Then [K2] = Selection.Value
      If Target.Column = 3 Then [L2] = Selection.Value
      'llama a macro de filtrado
      Call filtrandoAux
      End Sub

    Sub filtrandoAux()

    'establecer fin de rango

    filas = [A6].CurrentRegion.Rows.Count

    Range("A3:G" & filas).AdvancedFilter Action:=xlFilterInPlace, _

        CriteriaRange:=Range("J1:L2")

    End Sub

Para quitar el filtrado bastará con seleccionar alguna celda vacía dentro de las columnas A:C para que esas celdas vacías se vuelquen al rango de criterios.

Ahora, si la tabla es muy extensa (y no se visualizan filas vacías) se podría llamar al menú Datos, Filtros, Borrar.  Pero como desde esa opción no se quitarán los criterios y esto puede llevar a cierta confusión, lo mejor será ejecutar la siguiente macro desde el menú Desarrollador:

      Sub quitaFiltros()

      'para quitar los filtros, borrar primero los criterios

       [J2:L2] = ""

      ActiveSheet.ShowAllData

      End Sub

Acceso al VIDEO 35.

Desde el siguiente enlace se puede descargar el libro con la programación de los ejemplos, tanto del video 35 como del 36:

http://aplicaexcel.com/Blog/FiltroAvanzado.xlsm

 

34 - Abrir documentos vinculados desde Excel

En la entrada anterior (Hipervínculos-vínculos) vimos cómo guardar hipervínculos a sitios web, desde un control de Userform, y así poder llamarlos haciendo clic en la celda que contendrá ese enlace.
También vimos cómo vincular archivos de imagen a registros de una base de cualquier índole. Esta opción nos permite luego insertar esas imágenes en otros procesos Excel.
En esos casos utilizamos las instrucciones Hyperlink.Add y GetOpenFilename respectivamente.

Ahora vamos a ver cómo guardar nombre y dirección de documentos asociados a registros de una base. Utilizaremos nuevamente estas 2 instrucciones anteriores.
Además veremos otra macro para abrir esos archivos vinculados con un simple atajo de teclado. La instrucción utilizada será: FollowHyperlink.

Para ello partimos de una hoja base donde tendremos códigos e información de documentos y una columna para guardar el nombre del mismo (en pdf, doc o cualquier otro formato). Además guardaremos la ubicación de esos archivos en otra columna o en una celda auxiliar.

Utilizaré un Userform con controles para rellenar las columnas de datos, un botón para BUSCAR el archivo asociado y 2 botones de guardado, ya sea que guardemos la ubicación como hipervínculo (se llamará desde el mismo enlace) o la guardaremos separando nombre del archivo y su ruta. Luego tendremos una macro para llamar y abrir el documento elegido.

La macro del botón BUSCAR será la siguiente:

Private Sub CommandButton11_Click()    

miDoc = Application.GetOpenFilename(Title:="Selecciona tu archivo")

'si la variable está vacía significa que cancelamos la ventana de diálogo

If miDoc = False Then

    TextBox2 = ""

Else

    TextBox2 = miDoc

End If

End Sub

Para GUARDAR los registros tendremos 2 opciones: como hipervínculo o como nombre+ubicación del archivo.

Private Sub CommandButton1_Click()       'guardar con hipervínculo

With Sheets("Hoja3")

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

    .Range("A" & x) = Application.WorksheetFunction.Max(.Range("A:A")) + 1

    .Range("B" & x) = TextBox1

    .Range("C" & x) = ComboBox2

    .Range("D" & x) = ComboBox1

    .Range("E" & x) = TextBox2

    'hipervínculo en celda col E

    If TextBox2 <> "" Then _

        .Hyperlinks.Add Anchor:=.Range("E" & x), Address:=.Range("E" & x).Value, _

            ScreenTip:="ver doc", TextToDisplay:=.Range("E" & x).Value

End With

'limpia controles para un nuevo registro

ComboBox1.ListIndex = -1: ComboBox2.ListIndex = -1

TextBox1 = "": TextBox2 = ""

TextBox1.SetFocus

End Sub

Private Sub CommandButton2_Click()   'guardar solo nbre archivo

    With Sheets("Hoja3")

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

    .Range("A" & x) = Application.WorksheetFunction.Max(.Range("A:A")) + 1

    .Range("B" & x) = TextBox1

    .Range("C" & x) = ComboBox2

    .Range("D" & x) = ComboBox1

    'guardar nbre de archivo

    .Range("E" & x) = Dir(TextBox2)     'solo nombre del archivo

    'obtener la ruta

    '.Range("F" & x) = Left(TextBox2, InStr(1, TextBox2, Dir(TextBox2)) - 1)

End With

'limpia controles para un nuevo registro

ComboBox1.ListIndex = -1: ComboBox2.ListIndex = -1

TextBox1 = "": TextBox2 = ""

TextBox1.SetFocus

End Sub

En caso de haber guardado la ubicación del archivo relacionado como hipervínculo no necesitaremos ninguna macro para abrir ese documento. Con hacer clic sobre la celda de la col E será suficiente.
En cambio si guardamos el nombre+ubicación podemos llamarlos desde la siguiente macro. Para mayor comodidad le asignamos un atajo de teclado.
La macro se ubica en un módulo y se ejecutará estando seleccionada alguna celda de la col B y que no esté vacía.

Sub abriendoArchivo()

'Atajo de Teclado: CTRL f

'solo se ejecuta con celda seleccionada en col B

If ActiveCell.Column <> 2 Or ActiveCell = "" Then Exit Sub

'asignamos la ruta o carpeta donde se encuentran los PDF.

'Optar por una de las 3 instrucciones

'ruta = ThisWorkbook.Path & "\ESCANEADOS\"   'en subcarpeta del libro activo

ruta = [F1]                                                                   'en celda auxiliar F1

'ruta = ActiveCell.Offset(0, 4)                                    'en col F del registro

On Error Resume Next

ActiveWorkbook.FollowHyperlink ruta & ActiveCell.Offset(0, 3)

End Sub

'otra opción podría ser evaluar si col F está vacía y en ese caso tomar la ruta indicada en F1.

'If ActiveCell.Offset(0, 4) = "" Then

    'ruta = [F1]

'Else

   ' ruta = ActiveCell.Offset(0, 4)

'End If

Descargar libro de ejemplos desde aquí

Acceso al VIDEO 34.

32-33 - Hipervínculos - Vínculos

En esta entrada veremos cómo dejar hipervínculos para acceder a sitios web. También aprenderemos a vincular nuestros datos en Excel con otros archivos (imágenes, pdf, etc) para ser llamados desde otros procesos.

CASO 1:
Desde una hoja Excel, llenaremos una tabla de temas que vincularemos a sitios web.

Se trabajará con un formulario o Userform, donde pegaremos la dirección del sitio en un control TextBox. Luego al guardar el contenido de los controles en la hoja, en la col E además le insertaremos el hipervínculo con el siguiente código.

La instrucción será: nombre_de_hoja.HYPERLINKS.ADD

Private Sub CommandButton1_Click()   'GUARDAR

With Sheets("UF-Sitios")

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

    .Range("A" & x) = Application.WorksheetFunction.Max(.Range("A:A")) + 1

    .Range("B" & x) = ComboBox1

    .Range("C" & x) = ComboBox2

    .Range("D" & x) = TextBox1

    .Range("E" & x) = TextBox2

    'hipervínculo en celda col E

    .Hyperlinks.Add Anchor:=.Range("E" & x), Address:=.Range("E" & x).Value,  TextToDisplay:="ver sitio"

End With

'limpia controles para un nuevo registro

ComboBox1.ListIndex = -1: ComboBox2.ListIndex = -1

TextBox1 = "": TextBox2 = ""

ComboBox1.SetFocus

End Sub

CASO 2:
En una hoja Excel tendremos una tabla de productos, con todos sus datos en diferentes columnas añadiendo una columna para el nombre de la imagen asociada y otra para la ruta de esa imagen vinculada.
Esta opción será de utilidad para crear catálogos, recetarios y todos aquellos informes donde además de datos debiera mostrarse una imagen relacionada.

En el formulario tendremos 2 controles para la imagen: un TextBox para guardar la ruta completa y un control IMAGE donde mostrar la imagen.

El botón BUSCAR nos permitirá navegar por el equipo para encontrar la imagen a vincular. Se puede filtrar la búsqueda indicando algunas extensiones como jpg o bmp.

NOTA: no será posible presentar un archivo 'png' en un control Image.

Para la búsqueda utilizaremos el método GETOPENFILENAME.
Para separar solo el nombre del archivo la instrucción DIR.
Para obtener solo la ruta se extrae del texto completo solo la parte hasta el inicio del nombre del archivo con las funciones: LEFT e INSTR

El código completo del botón BUSCAR es el siguiente:

Private Sub CommandButton11_Click()

mifoto = Application.GetOpenFilename("Formato(*.jpg; *.bmp),*.jpg; *.bmp", Title:="Selecciona tu imagen")

'si la variable está vacía significa que cancelamos la ventana de diálogo

If mifoto = False Then

    TextBox25 = ""

Else

    Image1.Picture = LoadPicture(mifoto)

    nbreFoto = Dir(mifoto)     'nbre de la foto

    rutax = Left(mifoto, InStr(1, mifoto, nbreFoto) - 1)

    TextBox25 = rutax

End If

End Sub

Al GUARDAR, además del contenido de los controles, se guardará el nombre del archivo guardado en la variable (nbreFoto) que se declara al inicio del Userform para que pueda ser utilizada en las 2 subrutinas
Esto se logra con la instrucción:  Dim nbreFoto As String  

Dim hoi                     'nbre de la hoja declarada en el evento Initialize

Dim nbreFoto As String     'nbre de la foto

Private Sub CommandButton1_Click()   'GUARDAR

'completo columna con datos fijos

ini = hoi.Range("A2").End(xlDown).Row + 1

hoi.Range("A" & ini) = ComboBox1

hoi.Range("B" & ini) = TextBox1

'guarda nbre y ruta de la imagen

hoi.Range("E" & ini) = nbreFoto

hoi.Range("F" & ini) = TextBox25

'limpia el UF para un nuevo ingreso

ComboBox1.ListIndex = -1

TextBox1 = "": TextBox25 = ""

Image1.Picture = LoadPicture("")

ComboBox1.SetFocus

End Sub

El guardar imágenes asociadas a una base de productos nos permitirá armar luego un catálogo, con otro proceso que las ubicará y dimensionará en las celdas correspondientes. Por ejemplo:

Descargar el libro con formularios completos desde aquí.

Acceso a VIDEOS 32  y  33.

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.