☰

Заменить ссылки на ячейки значениями без F9 с помощью макроса

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
Заменить ссылки на ячейки значениями без F9 с помощью макроса

Это вопрос по макросу Excel. Дан ряд формул, например, таких: 

= A1 + A2
= B2 + B2

и т. д., ...

Я хотел бы заменить каждую из этих ссылок на ячейки значениями, не заменяя всю ячейку.

= 1 + 2
= 3 + 4

Способ сделать это вручную — выделить каждую ссылку в формуле и нажать , F9чтобы заменить ссылки значениями. Однако мне нужно сделать это для тысяч ячеек. Есть ли макрос, чтобы сделать это автоматически?

решение1

Следующий модуль берет диапазон и заменяет ссылки на ячейки значениями, найденными в указанных ячейках. Используется Range.Precedentsдля получения адресов указанных ячеек в строку и Splitпреобразования ее в массив адресов ячеек. Затем этот массив используется для поиска и замены совпадающего текста в формуле целевой ячейки.

Применение

  • Скопируйте весь код в стандартный модуль кода в редакторе Visual Basic.
  • Вызовите подпрограмму с помощью Call ReplaceReferencesWithValues(yourRange).

Макрос Testвызывает подпрограмму для замены ссылок в выбранном диапазоне.

Код

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

Обеспокоенность

  • Замена происходит как Stringи как таковая, если формула будет содержать как ссылку наклетка«A1» и другой текст, включающий «A1», соответствующий текст также будет заменен значениемклетка«А1».
  • Формулы, для работы которых требуется диапазон, например SUM, не будут работать, если вы попытаетесь заменить ссылки значениями ячеек.

решение2

Хотя данное решение @natancodes кажется возможным, исходный вопрос не является простой задачей, поскольку он заменяет текст на текст. Он сломается в этом простом случае: формула ищет A1, когда появляются A1, A11, AA11 или AA1, ссылки будут пытаться перезаписать неверными значениями, и макрос даже не будет работать (кроме On Error Resume Next). Лучше использовать RegEx для операции поиска и замены. Выражение RegEx приведено здесь, но из-за отсутствия уверенности в выражениях не совсем уверен, что все еще работает, и необходимо дальнейшее тестирование: ('?[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})? (оно пришло изhttps://www.get-digital-help.com/extract-cell-references-from-a-formula/).

Другой путь, подробно описанный здесьhttps://fastexcel.wordpress.com/2013/10/27/анализ-функций-из-формул-Excel-использование-vba-is-mid-or-a-byte-array-the-best-method/, кажется немного громоздким, но может сработать в этих особых случаях.

Связанный контент