15 - Utilizar fórmulas mediante VBA

En ocasiones necesitamos 'armar hojas' a las que ya colocaremos algunas fórmulas.
Si esta tarea la realizamos mediante programación necesitamos conocer las instrucciones según el tipo de fórmula que utilicemos.

Veamos 3 modos de escribir fórmulas y luego haciendo uso de un 'ayudante'.

1- Utilizar una fórmulas pero registrar el resultado en la celda.
       [O12] = Application.WorksheetFunction.WeekNum([J12])

En esta instrucción estamos diciendo que O12 sea igual al resultado que nos devuelva la función: Num.de.Semana (número de semana) de la fecha ubicada en J12.

2- Colocando una fórmula:
       [O12].Formula = "=WEEKNUM(J12)"

NOTA: la lista completa de funciones (inglés-español) la pueden descargar de la sección Fórmulas de mi sitio web.

3- Colocando una fórmula con notación R1C1, es decir en referencia a la ubicación de la celda.
      [O12].FormulaR1C1 = "=WEEKNUM(RC[-5])"

Aquí le estamos diciendo que la celda con la fecha se encuentra en la misma fila (R) y 5 columnas a izquierda de la columna O 

Estas 3 notaciones permiten también el uso de variables como veremos a continuación:

Ejemplo 1:

Tenemos una macro que filtra una hoja base y a continuación del resumen obtenido colocaremos una fórmula para obtener el total de registros filtrados.

La fórmula utiliza la función CONTARA y el rango va desde la fila 12 hasta la última celda ocupada.

En primer lugar necesitamos establecer cuál es la última celda, que la busco desde la col D.

          x = Range("D" & Rows.Count).End(xlUp).Row

Para este ejemplo la fórmula sería: =CONTARA(C12:C17)

Caso 1: dejar el resultado de la fórmula:
             [C26] = Application.WorksheetFunction.CountA(Range("C12:C" & x))

Caso 2: utilizar la sintaxis FORMULA solamente:
             [C26].Formula = "=COUNTA(C12:C" & x & ")"

Caso 3: utilizar la sintaxis FORMULAR1C1:
              'calcula a cuántas filas por encima de la 26 se ncuentra la última del rango
              y = 26 - x   
             [C26].FormulaR1C1 = "=COUNTA(R[-14]C:R[-" & y & "]C)"


Ejemplo 2:

Si necesitamos colocar una fórmula más compleja utilizaremos la grabadora de macros para obtener las instrucciones precisas.

 - Para ello ya tendremos escrita (o copiada) la fórmula en la primer celda donde la necesitemos.

 - Encendemos la grabadora de macros (con botón ubicado en extremo inferior izquierdo de la hoja o desde el menú Desarrollador, Macros). Con opción de crearla en 'Este libro'.

 - Colocamos el cursor en la barra de fórmulas y damos Enter.

 - Detenemos la grabadora. En el Editor de macros la encontraremos en algún módulo.

      Sub Macro1()
      '
      ' Macro1 Macro
      '
     ActiveCell.FormulaR1C1 = _
        "=IF(RC[3]=""Pendiente"",""Vencida            ""&TEXT(------------------) & ""
     Range("L2").Select
     End Sub

Ahora solo nos resta ajustarla para incluir las variables si fuesen necesarias. Recordar que la sintaxis debe presentar este formato: entre comillas la parte fija y entre & la parte variable.
Por ejemplo:
                      [C28].FormulaR1C1 = "=COUNTA(R[-16]C:R[-" & y & "]C)"

Acceso al VIDEO N° 15.