Fórmula con referencia absoluta en rango variable
Los expertos,
Estoy empezando a meterme con más profundidad en las macro y VBA y ya he podido solucionar muchas cosas adaptando soluciones para otros usuarios. La verdad que sois geniales.
Pero ahora me he topado con un problema que no consigo solucionar:
Tengo una hoja con una tabla a la que se van añadiendo más tablas según necesidad. Para ello he creado un botón que copia una tabla vacía (preparada en otra hoja) a esta hoja por debajo de la primera (con sus cabeceras, etc.). El problema viene a partir de añadir una segunda tabl, ya que dentro de una de las celdas de cada tabla hay una fórmula que hace referencia a una celda que se encuentra por encima (fuera de la propia tabla) de cada grupo de tabla. Al añadir una nueva línea en la segunda (y consecutivas) tabla se pierde la celda de referencia. Sé que el origen está en la cantidad de líneas que se han añadido a la tabla anterior, pero no consigo escribir nada que le diga que "actualice" la fórmula con la nueva posición de la celda de referencia.
Os pongo dos fotos para que veáis mejor lo que intento explicar:
Espero que alguien de vosotros tenga una genial idea de como solucionarlo.
Esas segundo, y más, tablas, ¿usar copiar/pegar de VBA para pasarla de una hoja a otra o usas algún otro mecanismo?
Pregunto porque si es solo copiar/pegar y el espacio final corresponde, en número de celdas, al inicial, no debería haber problemas con las formulas de esas celdas ¿u olvidaste darnos algún otro detalle?
Abraham Valencia
Hola Abraham, acabo de ver tu respuesta.
Lo de copiar y pegar lo hago con una macro.
Si quieres, te mando el fichero para que veas el lío que he montado.
El detalle que te faltará será que tengo una hoja donde está la tabla limpia de donde la macro copia y la pega en la hoja principal.
Creo que sí será más fácil entenderte viendo el archivo. Colócalo en OneDrive o Google Drive o donde te convenga.
Abraham Valencia
Hola Abraham
Aquí va el enlace:
https://drive.google.com/open?id=1eGhqLfe67t9hMX_zlQLZhvsgo52L45tc
Habrá mucho caos de trocitos de códigos, pero soy nueva en esto y es hasta donde he podido llegar para que me salgan las cosas que pretendo conseguir.
Todavía no está terminado, ni bloqueadas las celdas y hojas "intocables".
Te explico un poco para qué sirve:
Es para cálculo de pesos y cantidades de perfiles estructurales. En la hoja "consulta" se introduce un código y medidas en la primera tabla y se van calculando las cantidades y pesos de los conjuntos. Hay unos saltos de celda según el código y un botón para añadir más líneas.
Hay otro botón para añadir una nueva tabla vacía (que viene de otra hoja) por debajo de la anterior (que puede tener una línea o xxx). Ahí es donde me falla la fórmula.
Luego hay otro botón que me copia todo lo que se ha generado en otra hoja para poder hacer sumas totales y dejarlo "limpio y bonito".
Ya me contarás
Gracias
Hola Abraham,
aquí va el enlace:
https://drive.google.com/open?id=1eGhqLfe67t9hMX_zlQLZhvsgo52L45tc
gracias otra vez
Tu forma de ordenar las cosas es un poco particular, lo que muchas veces, y sobre todo a mediano o largo plazo, dificulta operaciones u otras cosas pero, eso está en tus manos.
Sobre el problema, propiamente dicho, cuando copias tu "tabla" de la hoja "Tabla Limpia", claro, copia con todo y fórmulas y, digamoslo de un modo, todo encaja a la perfección la primera vez, pero ojo a un detalle: Las fórmulas son relativas ¿y qué pasa con ese detalles? Al insertar nuevas filas, como haces después, al ser relativas las fórmulas, estás "corren" sus valores, generando el problema que has puesto en cuestión.
¿Cómo solucionarlo? Ya comenté que tu forma/orden es muy particular y por eso mismo recomiendo una forma, también, particular de resolverlo: Una vez copiada/pegada cada tabla nueva, convirtamos la fórmula del problema en una mezcla de absoluta y relativa (mixta). Insisto en que yo lo haría de otra forma pero prueba así:
Sub InsertarConjunto()
Dim TuFormula As String, NuevaFormula As String, Celda As String
Dim Numero As Long
'guardamos en variable la primer fila libre de la hoja CONSULTA
libre = Sheets("CONSULTA").Range("C65536").End(xlUp).Row + 2
'guardamos en variable la última fila ocupada de la hoja Tabla limpia
finfila = Sheets("Tabla limpia").Range("C65536").End(xlUp).Row
Sheets("Tabla limpia").Select
Rows("4:9").Select
Selection.Copy Destination:=Sheets("CONSULTA").Range("A" & libre)
Sheets("CONSULTA").Select
Application.CutCopyMode = False
'ActiveCell.Offset(4, -3).Select
Let Celda = Sheets("CONSULTA").Range("L65536").End(xlUp).Address
Let TuFormula = Range(Celda).Offset(-2, 0).Formula
Let Numero = Mid(TuFormula, InStr(1, TuFormula, "*") + 2, Len(TuFormula) - InStr(1, TuFormula, "*"))
Let NuevaFormula = Mid(TuFormula, 1, InStr(1, TuFormula, "*")) & "$F$" & Numero
Range(Celda).Offset(-2, 0).Formula = NuevaFormula
End SubSolo por si acaso, borra la anterior macro de ese nombre y reemplaza por la que envío ahí. He respetado todo lo demás que tú tienes ahí, para que no te confundas, básicamente he agregado el cambio en la fórmula. Cuando comiences a usarla, a partir de ese momento, cuando insertes líneas ya no tendrás el problema.
Salu2
Abraham Valencia
Hola Abraham,
Mil y una gracias por tu ayuda.
Con tus cambios funciona a la perfección.
Sé que hay mucho lío en lo que estoy haciendo, pero es la primera vez que hago algo con VBA en Excel y voy avanzando conforme mi jefe me dice qué más cosas le gustaría que tuviera el fichero.
Siempre es culpa de los jefes jejeje. De nada y feliz año estimado :)
Abraham Valencia
