☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
Substitua referências de células por valores sem F9 usando macro

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

= A1 + A2
= B2 + B2

etc., ...

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

= 1 + 2
= 3 + 4

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

Responder1

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

Uso

  • Copie todo o código para um Módulo de Código Padrão no Editor do Visual Basic.
  • Chame a sub-rotina com Call ReplaceReferencesWithValues(yourRange).

A Testmacro chama a Subrotina 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 acontece como a Stringe como tal, se a fórmula contiver uma referência aocélula"A1" e outro texto incluindo "A1", o texto correspondente também seria substituído pelo valor decélula“A1”.
  • As fórmulas que requerem um intervalo para funcionar, como SUM, serão interrompidas se você tentar substituir as referências por valores de células.

Responder2

Embora a solução @natancodes pareça viável, a questão original não é uma tarefa simples porque está substituindo texto por texto. Ele irá quebrar neste caso simples: fórmula procurando por A1 quando aparecer A1, A11, AA11 ou AA1, as referências tentarão sobrescrever com valores errados e a macro nem funcionará (além de On Error Resume Next). É melhor usar um RegEx para a operação de pesquisa e substituição. Uma expressão RegEx é fornecida aqui, mas devido à falta de confiança nas expressões, não tenho certeza se ainda funciona e precisa de mais testes: ('?[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})? (Veio dehttps://www.get-digital-help.com/extract-cell-references-from-a-formula/).

Outro caminho, detalhado aquihttps://fastexcel.wordpress.com/2013/10/27/parsing-functions-from-excel-formulas-using-vba-is-mid-or-a-byte-array-the-best-method/, parece um pouco complicado, mas pode funcionar nesses casos especiais.

informação relacionada