☰

Reemplazar referencias de celda con valores sin F9 usando macro

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
Reemplazar referencias de celda con valores sin F9 usando macro

Esta es una pregunta macro de Excel. Dada una serie de fórmulas como las siguientes: 

= A1 + A2
= B2 + B2

etc., ...

Me gustaría reemplazar cada una de estas referencias de celda con los valores sin reemplazar toda la celda.

= 1 + 2
= 3 + 4

La forma de hacer esto manualmente sería resaltar cada referencia dentro de la fórmula y presionar F9para reemplazar las referencias con valores. Sin embargo, tengo que hacer esto con miles de células. ¿Existe una macro para lograr esto automáticamente?

Respuesta1

El siguiente módulo toma un rango y reemplaza las referencias de celda con valores que se encuentran en las celdas a las que se hace referencia. Se utiliza Range.Precedentspara obtener las direcciones de celda a las que se hace referencia en una cadena y Splittransformarla en una matriz de direcciones de celda. Luego, esta matriz se usa para buscar y reemplazar texto coincidente en la fórmula de la celda de destino.

Uso

  • Copie el código completo en un módulo de código estándar en el Editor de Visual Basic.
  • Llame a la subrutina con Call ReplaceReferencesWithValues(yourRange).

La Testmacro llama a la Subrutina para reemplazar las referencias en el rango seleccionado.

Código

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

Preocupaciones

  • El reemplazo ocurre como un Stringy como tal, si la fórmula contendría tanto una referencia alcelúla"A1" y otro texto que incluya "A1", el texto coincidente también se reemplazará con el valor decelúla"A1".
  • Las fórmulas que requieren un rango para funcionar, como SUM, se romperán si intenta reemplazar las referencias con valores de celda.

Respuesta2

Aunque la solución dada por @natancodes parece factible, la pregunta original no es una tarea sencilla porque consiste en reemplazar texto sobre texto. Se romperá en este caso simple: la fórmula que busca A1 cuando aparece A1, A11, AA11 o AA1, las referencias intentarán sobrescribirse con valores incorrectos y la macro ni siquiera funcionará (aparte de En caso de error, reanudar a continuación). Es mejor utilizar una expresión regular para la operación de búsqueda y reemplazo. Aquí se proporciona una expresión RegEx, pero debido a la falta de confianza en las expresiones, no estoy seguro de que aún funcione y necesita más pruebas: ('?[a-zA-Z0-9\s[].]{1,99})?' ?!?$?[a-zA-Z]{1,3}$?[0-9]{1,7}(:$?[a-zA-Z]{1,3}$?[0- 9]{1,7})? (vino dehttps://www.get-digital-help.com/extract-cell-references-from-a-formula/).

Otro camino, detallado aquíhttps://fastexcel.wordpress.com/2013/10/27/parsing-functions-from-excel-formulas-using-vba-is-mid-or-a-byte-array-the-best-method/, parece un poco engorroso, pero puede funcionar en estos casos especiales.

información relacionada