Substitua referências de célula por valores sem F9 usando macro

Question

Esta é uma questão de macro do Excel. Dada uma série de fórmulas como as seguintes:

= A1 + A2
= B2 + B2

, etc., ...

Eu gostaria de substituir cada uma dessas referências de célula pelos valores sem substituir a célula inteira.

= 1 + 2
= 3 + 4

A maneira de fazer isso manualmente seria destacar cada referência dentro da fórmula e pressionar F9 para substituir as referências por valores. No entanto, tenho que fazer isso por milhares de células. Existe uma macro para realizar isso automaticamente?

    

Answer 1

O módulo a seguir usa um intervalo e substitui as referências de célula pelos valores encontrados nas células referenciadas. Usa Range.Precedents para obter os endereços de célula referenciados para uma string e Split para transformá-los em uma matriz de endereços de células. Essa matriz é usada para localizar e substituir o texto correspondente na fórmula da célula de destino.

Uso

• Copie o código inteiro para um módulo de código padrão no Visual Basic Editor.
• Chame a sub-rotina com Call ReplaceReferencesWithValues(yourRange) .

A macro Test chama a sub-rotina para substituir as referências no intervalo selecionado.

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

Preocupações

• A substituição ocorre como String e, como tal, se a fórmula contiver uma referência à célula "A1" e outro texto incluindo "A1", o texto correspondente também será substituído pelo valor de célula "A1".
• Fórmulas que exigem um intervalo para funcionar, como SUM , serão interrompidas se você tentar substituir as referências por valores de células.