Insertar fórmula con referencias a otras celdas con un valor relativo que me permita arrasrtar sin usar Fx Indirecto.
Hoy estoy con una necesidad y quiero ver si me pueden ayudar... Tengo una macro que dicta:
Sub Buscar_Cuota_Mant()
Dim matriz As String
Dim columna As String
matriz = Range("P11").Value
columna = Range("P6").Value
Separador = Application.International(xlListSeparator)
Separador = ","
Range("C11").Formula = "=VLOOKUP(B11" & Separador & matriz & Separador & columna & Separador & "0)"
End Sub
Funciona muy bien pero deseo que mi matriz no sea el valor absoluto de una celda ya que luego de esta macro deseo arrastrar la fórmula hacia abajo y quiero que esa referencia sea un valor relativo pero no se como hacerlo, he pensado en la fórmula indirecto como una vez me sirvió pero esto me obliga a abrir los libros a los que se hace referencia y con esta macro no lo necesito hacer, uso office 2007 ahora no se si con otros office tendré problemas pero es por ahora lo que menos nos importa..
Pasa que la matriz que uso es el valor de una celda que contiene una formula de texto que es la siguiente ="'"&TEXTO($P$7;)&TEXTO(O11;)&TEXTO($P$8;) donde el texto1(P7) es la ubicación del archivo, el texto2(O11) es la hoja y el texto3(P8) es la matriz de celdas a evaluar. Lo que sucede es que en las celdas donde insertare la fórmula según esta macro depende si es C11, C12 y C13 cambia la hoja a 1, 2 y 3 respectivamente, por eso al insertarla en la primera celda quiero que al correrla hacia abajo solo cambiara la referencia de la hoja y nada más. Desde luego no son solo tres celdas sino 40 y por eso no quiero insertar una fórmula por cada una.
A ver en que me pueden ayudar respecto de eso, acepto otras soluciones aun cambiando las macros, creando fórmulas o insertando funciones que me resuelvan el resultado, je je...
1 Respuesta
Utiliza la siguiente opción:
Sub Macro18()
'Por.Dante Amor
libro = "[ejemplo.xlsx]"
hoja = 1
rango = "!A:B"
col = 2
sep = ","
For i = 11 To 50
Range("C" & i) = "=VLOOKUP(B11" & sep & libro & hoja & rango & sep & col & sep & "0)"
hoja = hoja + 1
Next
End SubCambia en la macro:
libro por el nombre de tu libro,
hoja = 1, por el número de hoja inicial, en tu ejemplo pusiste 1
rango = "!A:B", por el rango de columnas o celdas en la que se va a buscar
col = 2, por el número de columna que va a obtener la función buscarv
sep = "," por el separador que necesitas
For i = 11 To 50, cambia el 11 por la celda inicial y cambia 50 por la celda final.
Saludos. Dante Amor
Recuerda valorar la respuesta.
Muchar gracias por tu ayuda disculpa tanto tiempo sin contestar,
Intenté tu macro es genial, de veras que me ayudas mucho, gracias por tomarte la molestia, y bueno, para molestar más, tengo un caso.. coloque...
Sub Macro18()
'Por.Dante Amor
libro = Range("P7").Value
hoja = 1
rango = "'!A11:AH11"
col = 34
sep = ","
For i = 11 To 50
Range("C" & i) = "=VLOOKUP(B11" & sep & libro & hoja & rango & sep & col & sep & "0)"
hoja = hoja + 1
Next
End Sub
Sucede lo siguiente, primero como necesito que en las filas donde se insertan las fórmulas, sean con referencias relativas inserte la fórmula de esta manera:
Range("C" & i) = "=VLOOKUP(RC[-1]" & sep & libro & hoja & rango & sep & col & sep & "0)"
Y sucede que extrañamente funciona pero me inserta esta formula en la celda
=BUSCARV(B11;'[Cuentas.xlsm]1'!'A11':'AH11';34;0)
Me coloca el rango entre apóstrofos y no se por cual razón y obviamente no me funciona la búsqueda.
En la macro en rango yo tuve que colocar rango = "'!A11:AH11"
Si lo coloco sin el apostrofo no me hace la fórmula y me da error de sintaxis
Lo otro es que las hojas no siempre se llaman con números sucesivos, pueden ser alfanuméricos, ¿y sospecho que en otro libro distinto no me servirá la expresión "hoja + 1" o si?... bueno todas los nombres de las hojas están en el orden que existen en columna B desde las filas 11 a la 50 porque se llaman tal y como el valor buscado, no se si eso sirva...
¿Qué podría hacer?...
Lo que pasa es que tienes que usar la misma nomenclatura en toda la fórmula
Inicias la fórmula con esto:
Range("C" & i) = "=VLOOKUP(RC[-1]"
Pero en rango tienes esto:
rango = "'!A11:AH11"
Son dos nomenclaturas: RC y A
Te cambio la macro para que el valor de B11 cambie a B12, B13, etc:
Sub Macro18()
'Por.Dante Amor
libro = "[Cuentas.xlsm]"
rango = "!A11:AH11"
col = 34
sep = ","
For i = 11 To 50
nombrehoja = Range("B" & i)
Range("C" & i) = "=VLOOKUP(B" & i & sep & libro & nombrehoja & rango & sep & col & sep & "0)"
n = n + 1
Next
End SubNo es necesario que pongas el apóstrofo, la fórmula lo pone si el libro está cerrado.
Saludos. Dante Amor
Recuerda valorar la respuesta.
Muchas Gracias por tu ayuda Dante, de veras muy amable y me ha funcionado, y además aprendí algo nuevo, no sabia que si en una macro utilizaba una referencia relativa me cambiaba todo el sistema de referencia, gracias eso me ayudará mucho para mis próximas consideraciones.
Una cosa lo de los apóstrofos por alguna razón se me hacen obligatorios lo probé tal como me lo enviaste y me produce error... tuve que colocar lo con los apóstrofos, y te digo que lo intente con el libro abierto y cerrado jejeje...
Sub Macro19()
'Por.Dante Amor
libro = "'[Cuentas.xlsm]"
rango = "'!A11:AH11"
col = 34
sep = ","
For i = 11 To 50
nombrehoja = Range("B" & i)
Range("C" & i) = "=VLOOKUP(B" & i & sep & libro & nombrehoja & rango & sep & col & sep & "0)"
n = n + 1
Next
End Sub
Seguro es que hago algo mal, pero no importa, ya me quedo y estoy satisfecho eres muy buena ayuda de veras muchas gracias...
