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

Question

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

= A1 + A2
= B2 + B2

так далее., ...

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

= 1 + 2
= 3 + 4

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

Answer 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", соответствующий текст также будет заменен значением ячейки "A1".
• Формулы, которым требуется диапазон для работы, например, SUM , прервутся , если вы попытаетесь заменить ссылки значениями ячеек.