A raíz de un comentario que Gerardo dejó en
esta entrada, decidí armar un pequeño "combo" con una de las funciones mas utilizadas de Excel: Buscarv(valor, rango, columna, ordenado). No me explayaré sobre la función en si misma, dado que la gran mayoría de los usuario la conoce y domina, si no que veremos como insertarla desde una Macro, sin utilizar a Application.WorksheetFunction (
asi se usan)
Esta forma de trabajo nos permitirá sortear uno de los mayores problemas que enfrentamos a la hora de buscar datos: hacerlo sobre un libro que se encuentra cerrado.
Supongamos que en la hoja "datos" tenemos la pequeña y poco dietética tabla:
[+/-] Ver el resto / Ocultar
Si utilizamos a Buscarv dentro del mismo libro y la ingresamos en forma manual, digamos, no hay muchos misterios:
Por diversos motivos, quizás, necesitemos que esa función sea "dinámica" y que se coloque en una u otra celda según determinadas condiciones, lo cual dejaría sin efecto nuestro trabajo "manual" y ahí si debamos recurrir a las macros. Pero ¿como coloco una función desde VBA? Es mas sencillo de lo que parece.
Empecemos por lo mas fácil:
1) buscar datos dentro del mismo libro, en la misma hoja:
Sub InsertarFuncion()
Range("A7").Formula = "=Vlookup(1,A2:B5,2,0)"
End Sub
Ahá, así de fácil. Usamos la propiedad "Formula" del rango e insertamos la misma, entre comillas y comenzando con el signo "=".
2) buscar datos dentro del mismo libro, en otra hoja:
Sub InsertarFuncion2()
Range("A1").Formula = "=Vlookup(2,datos!A2:B5,2,0)"
End Sub
Si, exactamente: solo agregamos el nombre de la hoja, sin olvidar el signo de admiración ( ! ) antes del rango.
3) buscar datos en otro libro: aquí deberemos prestar especial atención a la forma en la cual armamos el segundo argumento, ya que debe respetar toda la ruta hasta llegar al rango de búsqueda, que debe incluir:
Unidad de disco / [Nombre de archivo] / Nombre de Hoja / Rango
Y ojo con los apostrofes. De esta forma no me ha fallado nunca:
Sub InsertarFuncion3()
Dim Ruta As String
'armo la ruta al archivo: antes de la unidad de disco y luego
'del nombre de la hoja van los apostrofes... si se omiten la
'macro arro jará error:
Ruta = "'C:\Users\damian\Desktop\[Libro2.xls]Hoja1'!$A$1:$B$4"
'ahora concateno la variable dentro de la función:
Range("a1").Formula = "=Vlookup(3, " & Ruta & ", 2, 0)"
End Sub
lo que me devuelve el tercer registro, segunda columna:
vean, en la barra de fórmulas, como nuestra variable "Ruta" se transcribió tal cual dentro de la función. Si, dice "victoria", lo cual no tiene nada que ver con la primer tabla que les mostré, pero es una segunda que armé para el ejemplo, con el nombre de mis hijas.
Sigamos. Esta última alternativa nos será de muchísima utilidad si necesitamos leer datos de una tabla que se encuentra en un libro cerrado, cuestión que a desvelado a mas de uno. ¿Contras? Si, como todo: atención a la cantidad de funciones que ingresan de esta forma, por que es sabido que un libro con muchas funciones se ralentiza en cada actualización, ya que debe recalcularlas a todas ellas. Una posible solución sería, por ejemplo: colocamos la función en A1, copiamos y luego hacemos pegado especial, pegando únicamente el valor. Y todo con macros.
Así como trabajamos con Buscarv nos resulta viable utilizar cualquier función de Excel, como por ejemplo Sumar.Si():
Sub InsertarFuncion4()
Range("e8").Formula = "=SumIf($D$1:$D$6, ""abril"", $E$1:$E$6)"
End Sub
Atención a esas dobles comillas del segundo argumento.
Y si, como no podría ser de otra forma, Excel no nos decepciona, todo sale bien.
Espero que les sea de utilidad, especialmente cuando deseen crear funciones que actúen sobre datos que están dentro de un libro cerrado.
Link al archivo.
Muchas Gracias! Me despejó muchas dudas...pense que habría una forma de utilizar application.worksheetfunction para resolver la búsqueda en un libro cerrado.
ResponderEliminarGracias de nuevo!
bueno, muchas gracias por avisar. personalmente no conozco otras formas de crear funciones desde VBA que deban leer celdas de un libro cerrado (que son el ejemplo que te dí y el uso de ADO), aunque esta es bastante versátil y práctica, pudiendo solucionar el problema sin inconvenientes.
ResponderEliminarun abrazo
Buenas noches damian encontre tu blog y se me hace fabuloso, espero no molestarte y que me puedas ayudar con un problema que ya me vuelve loco y es el siguiente: Estoy realizando una macro donde en un archivo selecciono ciertas filas y las pego en otro archivo, pero nesecito que se peguen en el ultimo dia o columna escrita, pero siempre me pega en el mismo lado, es decir no me actualiza en eldia siguiente donde deberia pegar, espero me puedas ayuadar te agrdezco mucho
ResponderEliminarAhora te respondo al Mail que me enviaste a la casilla de correo, luego levantaré un post al respecto.
ResponderEliminarGracias x tu mensaje.