¿Como elaborar saldos anteriores y finales? VBA Excel
Tengo dos formulas en excel que me construyen los saldos iniciales y finales de una tabla de movimientos contables. Son las siguientes:
- Saldos anteriores (Columna F):
=SI(FILA()=12;BUSCAR(A12;Saldos!A:A;Saldos!E:E);SI(A12=A11;I11;SI(A12<>A11;BUSCAR(A12;Saldos!A:A;Saldos!E:E);0))) - Saldos finales (Columna I):
=SI(O(ENTERO(IZQUIERDA(A12;1))=1;ENTERO(IZQUIERDA(A12;1))=5;ENTERO(IZQUIERDA(A12;1))=6);F12+G12-H12;F12-G12+H12)
Ambas funcionan una despues de la otra fila a fila en cascada hasta la ultima fila con datos en la tabla
Explico cada formula:
Saldos anteriores:
- SI(FILA()=12: Indica si la fila actual es la primera fila con datos de la tabla
- BUSCAR(A12;Saldos!A:A;Saldos!E:E): Si lo anterior es cierto, busca, en otra tabla de otra hoja, el saldo inicial de esa cuenta contable.
- SI(A12=A11;I11: Indica que, si la cuenta contable de la fila actual es igual al de la cuenta anterior, colocar el saldo final de la fila anterior.
- SI(A12<>A11;BUSCAR(A12;Saldos!A:A;Saldos!E:E): Si las cuentas contables son distintas, busca, en la otra hoja, el saldo inicial de la cuenta contable.
Saldos finales:
- SI(O(ENTERO(IZQUIERDA(A12;1))=1;ENTERO(IZQUIERDA(A12;1))=5;ENTERO(IZQUIERDA(A12;1))=6);: Indica si la cuenta es deudora
- F12+G12-H12: Indica las operaciones en caso que la cuenta sea deudora
- F12-G12+H12: Indica las operaciones en caso que la cuenta sea acreedora
La pregunta es: Es posible hacer lo mismo en VBA con una macro en un modulo? Las formulas funcionan sin embargo practicamente todo lo tengo hecho en VBA
1 Respuesta
Un modo rápido es colocar tus fórmulas mediante VBA y luego convertirlas a valor si no te interesa dejarlas en la hoja. Explico los pasos a continuación.
1 a - Si ya tenés las 2 fórmulas en sus celdas, digamos F2 e I2. Entonces encendé la grabadora de macros, colocá el cursor en la barra de fórmulas de F2 y presioná Enter.
1 b - Si aún o están las fórmulas en sus celdas, encendé la grabadora de macros y escribí las fórmulas donde correspondan.
2- Al detener la grabadora, en el Editor tendrás un módulo con instrucciones como esta, para F2:
Range("F12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ROW()=12,LOOKUP(RC[-5],Saldos!C[-5],Saldos!C[-1]),IF(RC[-5]=R[-1]C[-5],R[-1]C[3],IF(RC[-5]<>R[-1]C[-5],LOOKUP(RC[-5],Saldos!C[-5],Saldos!C[-1]),0)))"Y algo similar habrás obtenido para I2, que simplificándola un poco quedaría así:
Range("I12").FormulaR1C1 = _
"=IF(OR(INT(LEFT(RC[-8],1))=1,INT(LEFT(RC[-8],1))=5,INT(LEFT(RC[-8],1))=6),RC[-3]+RC[-2]-RC[-1],RC[-3]-RC[-2]+RC[-1])"3- A continuación en la macro se arrastran esas fórmulas hasta el fin de rango que ya sabrás cómo obtenerlo. En mi ejemplo utilizo una variable buscando el fin en col A.
4- El resto de las instrucciones copian el rango completo y lo pegan como valores. También aquí dejo para F la instrucción obtenida con la grabadora y para I algo más simplificado.
Sub Macro1()
'x Elsamatilde
'
'desactiva mensajes
Application.DisplayAlerts = False
'colocar fórmulas con grabadora.
Range("F12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ROW()=12,LOOKUP(RC[-5],Saldos!C[-5],Saldos!C[-1]),IF(RC[-5]=R[-1]C[-5],R[-1]C[3],IF(RC[-5]<>R[-1]C[-5],LOOKUP(RC[-5],Saldos!C[-5],Saldos!C[-1]),0)))"
Range("I12").FormulaR1C1 = _
"=IF(OR(INT(LEFT(RC[-8],1))=1,INT(LEFT(RC[-8],1))=5,INT(LEFT(RC[-8],1))=6),RC[-3]+RC[-2]-RC[-1],RC[-3]-RC[-2]+RC[-1])"
'obtener fin de rango
filx = Range("A" & Rows.Count).End(xlUp).Row
'copia fórmulas a todo el rango
Range("F12").AutoFill Destination:=Range("F12:F" & filx), Type:=xlFillDefault
Range("I12").AutoFill Destination:=Range("I12:I" & filx), Type:=xlFillDefault
'deja solo valores (con grabadora o simplificada)
Range("F12:F" & filx).Copy
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Range("I12:I" & filx)
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
'quita el modo de copiado/pegado y activa mensajes
Application.CutCopyMode = False
Application.DisplayAlerts = True
End SubEspero te sirva la idea y no olvides valorar la respuesta.
PD) El uso de la grabadora de macros lo tengo explicado en la sección Macros de mi sitio.
