Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Ejemplo de uso de “Do Loop” aplicado al relleno de plantillas de Costos.

| 0 comments

El comando “Do Loop” en macros, nos permite diseñar un proceso iterativo que estaría en ejecución mientras se cumpla, o se deje de cumplir (en caso de que así sea programado) ciertas condiciones.

En este caso, el comando “Do Loop” será usado, en un contexto de finanzas, para rellenar una plantilla de costos.

La plantilla inicialmente tendrá 3 rubros (columnas) por rellenar manualmente, el rubro de “Insumos” y el de “costos unitarios y el de “Unidades requeridas”.

Ejemplo:

Fila\columna b c d e F
5 Insumo (unidades) Costos unitarios (soles) Unidades requeridas: Costos totales Pago de igv
6 Madera (pie tablar) 200 5
7 Piedras (toneladas) 150 2

Luego, se usará el siguiente macros para rellenar los rubros: “costos totales” y “pago de igv”.

El macros es el siguiente:

Sub plantillas_de_costos

‘primero se selecciona la primera en el que debería ir el primer dato, es decir, siguiente el ejemplo anterior debería de ser la celda e6.

range(“E6”).Select

‘el comando “do while” trabajara bajando de celda en celda, y terminará hasta que la celda seleccionada no tenga  información que utilizar, es decir; que esté al lado de celdas vacías

Do While IsEmpty(ActiveCell.Offset(0, -1)) = False

‘La fórmula  está escrita en términos relativos, es decir, siempre multiplica la celda de la izquierda por la celda de 3 espacios a la izquierda

ActiveCell.FormulaR1C1 = “=RC[-3]*RC[-1]”

‘luego se mueve a la  celda de la derecha

ActiveCell.Offset(0, 1).Select

‘En esta nueva columna se multiplicará la celda de la izquierda (recién calculada) por el igv (0.18)

ActiveCell.FormulaR1C1 = “=RC[-1]*0.18”

‘Luego se posiciona nuevamente en la columna de costos para seguir con la siguiente serie de datos (en caso de que los haya).

ActiveCell.Offset(1, -1).Select

Loop

‘Si se terminó de calcular todos los costos y los pagos de igv entonces estamos en una celda debajo del último dato, con los siguientes comandos se aplica

‘la función de autosuma para que nos muestre los costos totales de todos los insumos.

Selection.End(xlUp).Select
Selection.End(xlUp).Select
range(Selection, Selection.End(xlDown)).Select
range(“e6”).End(xlDown).Offset(1, 0).Select

‘para la sumatoria, se debe de tomar un comando “dinámico” y no absoluto, es decir que pueda

‘cambiar de rango si es necesario, para eso se utiliza el comando VDIFF

‘Se determina cual es la primera celda de la sumatoria (la que no va a cambiar): e6

vRowTop = 6

‘Luego se determina que la suma vaya desde e6 hasta la última celda de datos, es decir una celda más arriba de la seleccionada.

vRowBottom = ActiveCell.Offset(-1, 0).Row

‘Con los rangos determinados se determina la variable VDIFF, que va desde las celdas e6 hasta la última celda con información.

vDiff = vRowBottom – vRowTop + 1

‘Luego se puede insertar la fórmula de sumatoria pero usando un rango dinámico, es decir con la variable VDIFF.

Selection.FormulaR1C1 = “=SUM(R[” & -vDiff & “]C:R[-1]C)”

‘Por último se agrega, en la celda de la izquierda, el título “costos totales” para determinar ese resultado.

ActiveCell.Offset(0, -1).Select

ActiveCell = “costos totales”

range(“E21”).Select

Selection.NumberFormat = “$ #,##0.00”

End Sub

Elaborado por: Guillermo Cervantes Brown

Deja un comentario

Required fields are marked *.


Excel Avanzado located at , Lima, Perú . Reviewed by usuarios rated: 4.7 / 5