Facebook Twitter Gplus Flickr Pinterest LinkedIn YouTube RSS
Home Excel vba Insertar fórmula desde código vba en Excel
formats

Insertar fórmula desde código vba en Excel

Al momento de crear nuestras propias macros siempre es recomendable utilizar el grabador de macros. Sobretodo cuando no sabemos o no conocemos cómo realizar alguna función de Excel.

Una de esas funciones es insertar fórmulas y funciones. El grabador nos permite crear macros donde se inserten fórmulas, la diferencia es que la fórmula vendrá con la referencia R1C1 (filas y columnas) y la función estará en inglés. Por ejemplo:

Sub FormulaGrabada()
'
' Macro1 Macro
' Macro grabada el 11/08/2010 por Sergio A Campos H
'
'
    Range("C2").FormulaR1C1 = "=COUNTIF(R[-2]C[-2]:R[1]C[-2],""VENTA"")"
End Sub

Pero si lo que queremos es escribir nuestra propia macro y escribir nuestras fórmulas en nuestro propio lenguaje podemos utilizar la propiedad FormulaLocal. Por ejemplo:

Sub FormulaEnCelda()
'FormulaLocal nos pemite escribir nuestras funciones en nuestro propio lenguaje
'y de la misma manera que la escribiríamos dentro de una celda.
'
Range("C3").FormulaLocal = "=CONTAR.SI(A1:A4,""VENTA"")"
'
End Sub
 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
35 Comments  comments 
  • potter

    Gracias por el aporte aun a esta fecha sirven estos post. mil gracias…

  • Jordi Baye

    Ok, me ha sido de mucha ayuda. Gracias

  • JoaoM

    Buena esta. Gracias PREGUNTO:

    Quiero escribir la formula en un TexBox que tengo en una hoja (NO FORM) y por medio de un buton, insertar esa formula en una determinada celda, ultima fila(celda) vacia
    No me vale una celda suplementaria porque me afecta los datos anteriores existentes

  • JoaoM

    Ejemplo de la formula es este
    =SI($I5=””;””;$I5*$1,20)

    • http://about.me/sergioacamposh sergioacamposh

      Sinceramente no encuentro alguna razón para que se quiera escribir una fórmula en un Textbox y de ahí a una celda, pero si eso es lo que deseas realizar puede hacer algo como:

      Range(“A1″).value = Sheets(1).TextBox1.value

  • JORGE PALACIOS

    Buenos Dias Sergio, me podrias indicar como hago para insertar una funcion en una columna sin que me aparezca como llena de datos.
    lo que pasa es que concateno varias columnas en la columan A, y cuando voy a agregar datos tengo un codigo que me identifica la ultima fila vacia y alli me ingresa los datos. pero si le aplico la funcion a toda la columa . me sale un error o simplemente no me deja agregar los datos por que las filas aparecen con datos debido a la funcion

    la funcion es

    = CONCATENAR(B2;” “;C2;” “;D2;” “;F2)

  • http://about.me/sergioacamposh sergioacamposh

    Si quieres aplicar la función para filas en una columna puedes probar con esto:

    Sub test()
    Range(“A2:A10″).FormulaLocal = “=CONCATENAR(B2,”” “”,C2,”” “”,D2,”” “”,F2)”
    End Sub

    Saludos !!

  • JORGE PALACIOS

    Saludos Sergio, aprovechando tu gran ayuda. quiero preguntarte algo.

    lo que pasa es que debo ingresar una funcion a la columna A.
    tengo el codigo para agregar datos a las columnas B,C,D,E,F y G.
    el codigo identifica la ultima celda vacia y me llena los datos, la idea es que
    antes que me agregue datos me copie la funcion en la columna A y seguido pues me
    concatena los datos que agrego.

    por la granadora de macros me sale

    Selection.AutoFill Destination:=Range(“A2355:A2356″), Type:=xlFillDefault
    Range(“A2355:A2356″).Select

    la funcion es

    = CONCATENAR(B2356;” “;C2356;” “;D2356;” “;F2356)

    tu ayuda me dice q es algo asi

    Sub test()
    Range(“A2:A4000″).FormulaLocal = “=CONCATENAR(B2,”” “”,C2,”” “,D2,”” “”,F2)”
    End Sub

    mi codigo es (lo que necesito es poder copiar la funcion a la ultima fila vacia donde me va a guardar los datos nuevos en la columna A, )

    Private Sub cmdcrearproducto_Click()

    Application.ScreenUpdating = False

    Dim producto As String
    Dim presentacion As String
    Dim marca As String
    Dim iva As String
    Dim lote As String
    Dim existencias As String

    Dim I As Double

    producto = TextBox1.value
    presentacion = TextBox2.value
    marca = TextBox3.value
    iva = TextBox6.value
    lote = TextBox4.value
    existencias = TextBox5.value

    Range(“B2″).Select

    For I = 1 To 4000
    If ActiveCell.value = “” Then
    ultimafila = ActiveCell.Row – 1
    GoTo Continuar
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Next I
    Continuar:

    Cells(ultimafila + 1, 2) = producto
    Cells(ultimafila + 1, 3) = presentacion
    Cells(ultimafila + 1, 4) = marca
    Cells(ultimafila + 1, 5) = iva
    Cells(ultimafila + 1, 6) = lote
    Cells(ultimafila + 1, 7) = existencias

    TextBox1.value = “”
    TextBox2.value = “”
    TextBox3.value = “”
    TextBox4.value = “”
    TextBox5.value = “”
    TextBox6.value = “”

    Application.ScreenUpdating = True

    Unload FRMCREARPRODUCTO

    ActiveWorkbook.Save

    MsgBox (“El producto ha sido creado.”)
    ‘ ORDENARPRODUCTOS Macro


    Cells.Select
    ActiveWorkbook.Worksheets(“PRODUCTOS”).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(“PRODUCTOS”).Sort.SortFields.Add Key:=Range( _
    “A2:A4000″), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets(“PRODUCTOS”).Sort
    .SetRange Rows(“1:4000″)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True

    End Sub

    • http://about.me/sergioacamposh sergioacamposh

      por lo que te recomendaba que le des una leída a manuales en Internet es por sabes manejar varibles:

      Tenemos claro que cuando agregas tus datos la última celda cambia y así constantemente, entonces para saber cuál es la última celda, regularmente yo uso la función CONTARA para que me cuente las celdas llenas y por consiguiente el resultado será la última fila, y ese número es el que me va a definir hasta qué fila copiaré los datos. Te paso la macro que debes integrar a tu desarrollo:

      Sub AplicarFormula()
      UltimaFila = Application.WorksheetFunction.CountA(Range(“B:B”))
      Range(“A1″).Copy Destination:=Range(“A2:A” & UltimaFila)
      End Sub

      Adecúala a tus rangos.

  • JORGE PALACIOS

    Saludos Sergio al adecuarlo me queda así

    ultimafila = Application.WorksheetFunction.CountA(Range(“B2:B4000″))
    Range(“A2″).Copy Destination:=Range(“A2:” & ultimafila)

    ahi solo me identifica la ultima fila, pero no me copia la funcion = CONCATENAR(B2356;” “;C2356;” “;D2356;” “;F2356)

    OTRA OPCIÓN (en esta opción me revisa fila x fila y me ingresa la función concatenar pero se demora como 20 segundos. )
    lo que necesito es que me agregue la función a la ultima fila ingresada al guardar o crear los datos de las otras filas y me ordene todo la hoja o bd, lo que pasa es que la función me esta dando muchos líos por que cuando la ingresa y me ordena la función desaparece.

    Range(“A2″).Select
    While ActiveCell.Offset(0, 1) “”
    ActiveCell = ActiveCell.Offset(0, 1) & ActiveCell.Offset(0, 2) & ActiveCell.Offset(0, 3) & ActiveCell.Offset(0, 5) & ActiveCell.Offset(0, 6)
    ActiveCell.Offset(1, 0).Select
    Wend

    • http://about.me/sergioacamposh sergioacamposh

      Comparte por favor tu archivo donde sólo tengas los datos que quieres modificar con la macro, no tu archivo completo.

  • JORGE PALACIOS

    para enviarte el archivo debo estar inscrito a wordpress?
    no veo x por donde enviartelo
    te lo puedo enviar a tu correo?

    • http://about.me/sergioacamposh sergioacamposh

      Ya revisé tu archivo, y te comento que sin problema hubieras aplicado el código que te había mandado.

      El código que le agregué al botón GUARDAR fue:

      UltimaFila = Application.WorksheetFunction.CountA(Sheets(“PRODUCTOS”).Range(“B:B”))
      Sheets(“PRODUCTOS”).Range(“A2″).Copy Destination:=Sheets(“PRODUCTOS”).Range(“A2:A” & UltimaFila)
      Application.CutCopyMode = False

      Aquí puedes descargar tu archivo con la modificación https://skydrive.live.com/redir?resid=4509FEB32392C17C!2198

  • JORGE PALACIOS

    Saludos Sergio muchas gracias, como siempre agradezco tu tiempo y que compartas tus conocimientos….

  • JORGE PALACIOS

    Saludos Sergio, me podrias ayudar con una duda o problema menor que tengo.
    si quisiera espaciar la funcion en el siguiente codigo (algo como ” ” entre columnas)

    Range(“A2″).Select
    While ActiveCell.Offset(0, 1) “”
    ActiveCell = ActiveCell.Offset(0, 1) & ActiveCell.Offset(0, 2) & ActiveCell.Offset(0, 3) & ActiveCell.Offset(0, 5) & ActiveCell.Offset(0, 6)
    ActiveCell.Offset(1, 0).Select
    Wend

  • JORGE PALACIOS

    que pena este es el codigo

    Range(“A2″).Select
    While ActiveCell.Offset(0, 1) “”
    ActiveCell = ActiveCell.Offset(0, 1) & ActiveCell.Offset(0, 2) & ActiveCell.Offset(0, 3) & ActiveCell.Offset(0, 5) & ActiveCell.Offset(0, 6)
    ActiveCell.Offset(1, 0).Select
    Wend

    • http://about.me/sergioacamposh sergioacamposh

      Sólo une los “&” con un espacio.

  • Juan Camilo

    Hola Sergio.

    Me interesó mucho esta forma de realizar fórmulas a través de las macros de una manera tan sencilla. Solo tengo una pregunta… puedo guardar el resultado de la formula en una variable sin necesidad de dejar el calculo de la formula en una celda de la hoja de excel?

    • http://about.me/sergioacamposh sergioacamposh

      Por supuesto que se puede, sólo que ya no sería con FormulaLocal, sino con el objeto Application. El mismo casi quedaría así

      Numero = Application.WorksheetFunction.CountIf(Range(“A1:A4″), “VENTA”)

  • JORGE PALACIOS

    Saludos, a todos los lectores y a Sergio..

    tengo una pregunta, debo generar un reporte y no se en cual de los temas colocar mi consulta

    lo que pasa es que tengo que combinar 3 hojas de un libro (fact venta, nota credito, nota debito) estas estan asociadas por numero de fact de venta. lo que se requiere es que salga una fila los datos de fact de venta, en la siguiente o siguientes filas nota debito o credito asociada, y luego pues la siguiente fact de venta. todo eso que me lo ordene por ciudad,razon social, # fact

  • Juan Camilo

    Hola Sergio… otra pregunta … como hago para jugar con variables dentro de la formula asi como se puede hacer en la formula del tipo F1C1? ej:

    Range(“R2″).FormulaR1C1 = _
    “=IFERROR(INDEX(BD!R2C1:R” & ufd_bd & “C3,MATCH(report!RC[-17],BD!R2C2:R” & ufd_bd & “C2,0),3),””””)”

    • http://about.me/sergioacamposh sergioacamposh

      El siguiente ejemplo rellena de color las celdas de la columna A que tengan datos, y lo dinámico radia en que hace un conteo de las celdas con datos y el número lo toma como variable:

      CeldasConDatos = Application.WorksheetFunction.CountA(Range(“A:A”))
      Range(“A1:A” & CeldasConDatos).Interior.ColorIndex = 10

  • Juan Camilo

    Hola Sergio… últimamente como que he utilizado mucho tu conocimiento y lo agradezco enormemente. Necesito nuevamente tu ayuda… tengo unos archivos en una ruta especifica del C:, digamos que es el C:/ejemplo. En esa ruta tengo unos archivos de texto que tienen una sintaxis especial, por ej. el archivo 20120924.txt, donde la sintaxis es año, mes y dia. Aparte de este hay muchos archivos de texto que manejan la misma sintaxis. Como hago con una macro para leer el nombre de cada archivo y pescar cada parte de la descripcion como el año, mes y día y guardar cada valor en una variable? Te cuento que he intentado de muchas formas, pero no he tenido buenos resultados. Te agradecería mucho tu ayuda al respecto.

  • Christianr

    que Tal Sergio, espero puedas ayudarme. tengo un macro y necesito copiar celdas y pegarlas como formulas, te dejo mi macro paraque nos entendamos:
    Sub Calificaciones()
    Call llena_m ‘lee matriz
    Sheets(“Calificaciones”).Select
    Range(“c2″).Select
    renglon_alumnos = ActiveCell.End(xlDown).Row
    Sheets(“Sanroms”).Select
    Range(“d1″).Select

    For x = 1 To 19
    Sheets(“Calificaciones”).Select
    Range(columas(x) & “2:” & columas(x) & renglon_alumnos).Copy
    Sheets(“Sanroms”).Select
    ActiveCell.PasteSpecial xlPasteValues
    ActiveCell.Offset(renglon_alumnos – 1).Select

    Next

    End Sub
    me hace todo (copia y pega valores, pero necesito que me pegue formulas.
    Pdrias ayudarme.
    Gracias

    • Sergio Alejandro Campos

      Que tal Christianr:

      Sólo reemplaza esta línea

      ActiveCell.PasteSpecial xlPasteValues

      Por esta

      ActiveCell.PasteSpecial xlPasteFormulas

      Saludos !!

      • Christianr

        Muchas gracias por contestar, ya lo he intentado, pero no funcionó
        saludos

        • Sergio Alejandro Campos

          Que tal:

          Estás seguro que los datos que lee de esta línea contiene fórmulas?

          Range(columas(x) & “2:” & columas(x) & renglon_alumnos).Copy

          En todo caso podrías compartir el ejemplo funcionando en tu archivo para validarlo.

          • Christianr

            las celdas originales no contienen formulas, serán números, pero quiero que me pegue la referencia a esos numeros, no en sí los numeros.
            Gracias

          • Sergio Alejandro Campos

            Haberlo dicho desde el principio.

            El método para pegar vínculo es:

            ActiveSheet.Paste Link:=True

  • Javier

    Sergio, Intentado seguir tus recomendaciones tengo el siguiente código:

    Sub PatentesTCT()

    ‘ PatentesTCT Macro


    Sheets(“TCT”).Select
    Columns(“D:D”).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(“D1″).FormulaR1C1 = “Patentes1″
    Range(“D2″).FormulaLocal = “=si(o(extrae(c2,4,1)=””A””,extrae(c2,4,1)=””B””,extrae(c2,4,1)=””C””,extrae(c2,4,1)=extrae(c2,4,1)=””D””,extrae(c2,4,1)=””E””,extrae(c2,4,1)=””F””,extrae(c2,4,1)=””G””,extrae(c2;4;1)=””H””,extrae(c2,4,1)=””I””,extrae(c2,4,1)=””J””,extrae(c2,4,1)=””K””,extrae(c2,4,1)=””L””,extrae(c2,4,1)=””M””,extrae(c2,4,1)=””N””,extrae(c2,4,1)=””O””,extrae(c2,4,1)=””P””,extrae(c2,4,1)=””Q””,extrae(c2,4,1)=””R””,extrae(c2,4,1)=””S””,extrae(c2,4,1)=””T””,extrae(c2,4,1)=””U””,extrae(c2,4,1)=””V””,extrae(c2,4,1)=””W””,extrae(c2,4,1)=””X””,extrae(c2,4,1)=””Y””,extrae(c2,4,1)=””Z””),EXTRAE(C2,1,2)&DERECHA(C2,5),EXTRAE(C2,1,2)&” – “&EXTRAE(C2,6,4))”
    ‘Range(“D2″).FormulaR1C1 = _
    “=+IF(OR(MID(RC[-1],4,1)=””A””,MID(RC[-1],4,1)=””B””,MID(RC[-1],4,1)=””C””, MID(RC[-1],4,1)=””D””,MID(RC[-1],4,1)=””E””,MID(RC[-1],4,1)=””F””,MID(RC[-1],4,1)=””G””,MID(RC[-1],4,1)=””H””,MID(RC[-1],4,1)=””I””, MID(RC[-1],4,1)=””J””,MID(RC[-1],4,1)=””K””,MID(RC[-1],4,1)=””L””,MID(RC[-1],4,1)=””M””,MID(RC[-1],4,1)=””N””,MID(RC[-1],4,1)=””O””,MID(RC[-1],4,1)=””P””,MID(RC” & _
    “)=””Q””,MID(RC[-1],4,1)=””R””,MID(RC[-1],4,1)=””S””,MID(RC[-1],4,1)=””T””,MID(RC[-1],4,1)=””U””,MID(RC[-1],4,1)=””V””, MID(RC[-1],4,1)=””W””,MID(RC[-1],4,1)=””X””, MID(RC[-1],4,1)=””Y””, MID(RC[-1],4,1)=””Z””),MID(RC[-1],1,2)&MID(RC[-1],4,5),MID(RC[-1],1,2)&””-””&MID(RC[-1],6,4))”
    ‘For i = 2 To maxRow
    ‘Selection.AutoFill
    ‘Next
    Selection.AutoFill Destination:=Range(“D2:D1000000″)
    Range(“D:D”).Select
    End Sub

    Pero con el siguiente problema:

    1) Si ejecuto la formula local me dice que se produce un error tipo 13 (no coinciden los tipos)

    2) Si ejecuto la fórmula R1C1, por el contrario, se produce error tipo 1004, definido por la aplicación o el objeto.

    Espero puedas ayudar.

    Gracias!

    • Sergio Alejandro Campos

      Hola Javier:

      En la parte de la fórmula local has dos cuesiones a corregir:

      En esta parte extrae(c2;4;1) usas punto y coma y en el resto de la fórmula usas coma.

      En la última parte &" – "&EXTRAE(C2,6,4))" usas guión largo, pero para que la fórmula se aplique bien en la celda, debe ser guió corto.

      Tu código en esa parte de la fórmula quedaría así:

      Range("D2").FormulaLocal = "=si(o(extrae(c2,4,1)=""A"",extrae(c2,4,1)=""B"",extrae(c2,4,1)=""C"",extrae(c2,4,1)=extrae(c2,4,1)=""D"",extrae(c2,4,1)=""E"",extrae(c2,4,1)=""F"",extrae(c2,4,1)=""G"",extrae(c2,4,1)=""H"",extrae(c2,4,1)=""I"",extrae(c2,4,1)=""J"",extrae(c2,4,1)=""K"",extrae(c2,4,1)=""L"",extrae(c2,4,1)=""M"",extrae(c2,4,1)=""N"",extrae(c2,4,1)=""O"",extrae(c2,4,1)=""P"",extrae(c2,4,1)=""Q"",extrae(c2,4,1)=""R"",extrae(c2,4,1)=""S"",extrae(c2,4,1)=""T"",extrae(c2,4,1)=""U"",extrae(c2,4,1)=""V"",extrae(c2,4,1)=""W"",extrae(c2,4,1)=""X"",extrae(c2,4,1)=""Y"",extrae(c2,4,1)=""Z""),EXTRAE(C2,1,2)&DERECHA(C2,5),EXTRAE(C2,1,2) & "" - "" & EXTRAE(C2,6,4))"

      • Javier

        Gracias Sergio! Ahora mismo lo corrijo!

Sistema Wordpress corriendo bajo... Windows Server 2008 R2
Follow

Get every new post delivered to your Inbox

Join other followers