Следующий модуль принимает диапазон и заменяет ссылки на ячейки значениями, найденными в ссылочных ячейках. Использует 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
, прервутся , если вы попытаетесь заменить ссылки значениями ячеек.