Macro para rellenar celdas con una fórmula
Hola, mi caso es el siguiente: imaginaos el siguiente caso práctico, tengo un diferentes columnas, "Nºartículo" "Nombre artículo" y "Existencias". Parto de que sólo conozco el "Nºartículo", los otros datos los obtengo con un buscarv.
Mi duda es, utilizando la grabadora de macros cómo hago el buscarv para una cantidad de filas de Nºartículo desconocida, es decir, no sé en un principio si voy a rellenar a mano 5 referencias o 200, pero quiero que la macro me los busque todos. Si ya sé, la solución fácil es expandir el buscarv a todas las filas de la columna y luego borrar los #N/A, pero no queda nada "elegante".
Muchas gracias y un saludo
Mi duda es, utilizando la grabadora de macros cómo hago el buscarv para una cantidad de filas de Nºartículo desconocida, es decir, no sé en un principio si voy a rellenar a mano 5 referencias o 200, pero quiero que la macro me los busque todos. Si ya sé, la solución fácil es expandir el buscarv a todas las filas de la columna y luego borrar los #N/A, pero no queda nada "elegante".
Muchas gracias y un saludo
1 respuesta
Respuesta de Fran Riveiro
1
Existen varias posibilidades... una macro que te meta el código solo en donde sea necesario o bien combinar la función si con el buscarV, es decir, rellenas todas las celdas con la fórmula hasta donde quieras pero en vez de poner:
=buscarV(blablablabla)
pones:
=si(CeldaConElCodigo<>"";buscarV(blablabla);"")
Si quieres la macro solo pídela y te la pongo en un momento, pero vamos, si lo único que te molesta es que se vea el N/A, de esta forma no sale.
=buscarV(blablablabla)
pones:
=si(CeldaConElCodigo<>"";buscarV(blablabla);"")
Si quieres la macro solo pídela y te la pongo en un momento, pero vamos, si lo único que te molesta es que se vea el N/A, de esta forma no sale.
Hola,
muchas gracias darkwizard, por favor, puedes ponerme la macro a ver que tal funciona.
muchas gracias darkwizard, por favor, puedes ponerme la macro a ver que tal funciona.
Pues simplemente tienes que poner esta fórmula (modificándola para tu caso) en un botón o en cualquier cosa:
For Each celdita In ActiveSheet.Range("b5", Range("b5").End(xlDown))
celdita.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Hoja2!C[-1]:C,2,FALSE)"
Next celdita
Te explico para que la puedas modificar según tus necesidades.
El For each es un bucle que se recorre todas las celdas con códigos, ya que es lo que sabemos que existe, en mi caso los tenia a partir de la celda b5, es decir, en la celda b5 tenía el primer código conocido. Como no se cuantos códigos tengo lo que hago es que continuo el bucle hasta el último dato que hay debajo de b5, esto tiene el inconveniente de que si hay una celda en medio vacía se pararía ahí, por ello tienen que estar todos los códigos sin huecos en medio. Es decir los códigos tienen que ir de b5 a b500 seguidos, si en b200 no hay metido un código se pararía ahí el bucle.
celdita.offset(0,1) simplemente indica que en la celda de la derecha (0,1) se ponga la formula que va a continuación del =.
Si en tu caso la fórmula te la tiene que poner dos celdas a la derecha pues seria 0,2 si son diez pues 0,10, si es en la celda de la izquierda simplemente le pones el símbolo menos -, delante del número. El 0 lo que indica es la fila, así que ahí no tendrás que cambiar nada y será siempre 0 que indica que es en la misma fila.
Sobre la fórmula:
"=VLOOKUP(RC[-1],Hoja2!C[-1]:C,2,FALSE)"
Es el BuscarV normal, pero observaras que aparece "distinta" eso es porque al meterla desde visual hay que escribirla de esa manera.
Vlookup es el buscarV, luego entre paréntesis aparecen los campos normales.
RC[-1] es el dato que vamos a buscar, aquí al igual que en el offset, se indica todo por filas y columnas. Se supone que el dato que vamos a buscar esta en la misma fila, pero en distinta columna, con lo cual siempre será RC["algo"], como explique antes, si el dato que buscamos esta justo a la izquierda pues es C[-1] si esta dos columnas a la izquierda pues -2 si esta en la columna de la derecha pues seria 1 sin el símbolo de negativo...
El segundo parámetro es donde lo vamos a buscar.
Hoja2! C[-1]:C
En este caso lo que hice fue que los nombres de los artículos (o lo que estés buscando) se encuentran en otra hoja, Hoja2 es el nombre de la hoja donde se encuentran, tu tendrás que sustituirlo por el nombre de tu hoja, el símbolo ! Es obligatorio al tener que cambiar de hoja. Después vuelve a ser lo mismo de siempre, referencias por filas y columnas, en este caso una C sola indica que es la misma columna, aquí es donde puede haber más complicaciones, a si que lo explicare lo mejor que pueda para que no tengas ninguna duda.
Con esta macro vamos a hacer que meta en una celda en concreto que se encuentra en una fila y una columna (indicado por las letras y los números) pues bien, si en esta fórmula ponemos solo la C indica que estamos haciendo referencia a la misma columna en la que se encuentra la fórmula, y el -1 que es la anterior.
Imaginate que la fórmula la vamos a meter en la celda B5 pues con Hoja2!C[-1]:C estamos haciendo referencia a que busque en A y B, seria como poner en excel normal:
buscarv(a5:Hoja2!A:B;2;falso).
Ahí tendrás que modificarlo tu también según donde se encuentre la lista con los artículos y su nombre.
Sobre el 2 y el false, es como en excel normal, 2 nos indica cual es la columna que nos tiene que devolver y el falso es que en caso de que las celdas estuvieran ordenadas por algún criterio, lo ignoraría y devuelve el valor correcto.
Cualquier duda aquí estoy, no te olvides en cerrar la pregunta una vez se te haya solucionado la duda.
For Each celdita In ActiveSheet.Range("b5", Range("b5").End(xlDown))
celdita.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Hoja2!C[-1]:C,2,FALSE)"
Next celdita
Te explico para que la puedas modificar según tus necesidades.
El For each es un bucle que se recorre todas las celdas con códigos, ya que es lo que sabemos que existe, en mi caso los tenia a partir de la celda b5, es decir, en la celda b5 tenía el primer código conocido. Como no se cuantos códigos tengo lo que hago es que continuo el bucle hasta el último dato que hay debajo de b5, esto tiene el inconveniente de que si hay una celda en medio vacía se pararía ahí, por ello tienen que estar todos los códigos sin huecos en medio. Es decir los códigos tienen que ir de b5 a b500 seguidos, si en b200 no hay metido un código se pararía ahí el bucle.
celdita.offset(0,1) simplemente indica que en la celda de la derecha (0,1) se ponga la formula que va a continuación del =.
Si en tu caso la fórmula te la tiene que poner dos celdas a la derecha pues seria 0,2 si son diez pues 0,10, si es en la celda de la izquierda simplemente le pones el símbolo menos -, delante del número. El 0 lo que indica es la fila, así que ahí no tendrás que cambiar nada y será siempre 0 que indica que es en la misma fila.
Sobre la fórmula:
"=VLOOKUP(RC[-1],Hoja2!C[-1]:C,2,FALSE)"
Es el BuscarV normal, pero observaras que aparece "distinta" eso es porque al meterla desde visual hay que escribirla de esa manera.
Vlookup es el buscarV, luego entre paréntesis aparecen los campos normales.
RC[-1] es el dato que vamos a buscar, aquí al igual que en el offset, se indica todo por filas y columnas. Se supone que el dato que vamos a buscar esta en la misma fila, pero en distinta columna, con lo cual siempre será RC["algo"], como explique antes, si el dato que buscamos esta justo a la izquierda pues es C[-1] si esta dos columnas a la izquierda pues -2 si esta en la columna de la derecha pues seria 1 sin el símbolo de negativo...
El segundo parámetro es donde lo vamos a buscar.
Hoja2! C[-1]:C
En este caso lo que hice fue que los nombres de los artículos (o lo que estés buscando) se encuentran en otra hoja, Hoja2 es el nombre de la hoja donde se encuentran, tu tendrás que sustituirlo por el nombre de tu hoja, el símbolo ! Es obligatorio al tener que cambiar de hoja. Después vuelve a ser lo mismo de siempre, referencias por filas y columnas, en este caso una C sola indica que es la misma columna, aquí es donde puede haber más complicaciones, a si que lo explicare lo mejor que pueda para que no tengas ninguna duda.
Con esta macro vamos a hacer que meta en una celda en concreto que se encuentra en una fila y una columna (indicado por las letras y los números) pues bien, si en esta fórmula ponemos solo la C indica que estamos haciendo referencia a la misma columna en la que se encuentra la fórmula, y el -1 que es la anterior.
Imaginate que la fórmula la vamos a meter en la celda B5 pues con Hoja2!C[-1]:C estamos haciendo referencia a que busque en A y B, seria como poner en excel normal:
buscarv(a5:Hoja2!A:B;2;falso).
Ahí tendrás que modificarlo tu también según donde se encuentre la lista con los artículos y su nombre.
Sobre el 2 y el false, es como en excel normal, 2 nos indica cual es la columna que nos tiene que devolver y el falso es que en caso de que las celdas estuvieran ordenadas por algún criterio, lo ignoraría y devuelve el valor correcto.
Cualquier duda aquí estoy, no te olvides en cerrar la pregunta una vez se te haya solucionado la duda.
