☰

用单元格引用替换范围

tag-icon • tag-icon microsoft-excel vba
用单元格引用替换范围

我有数百个包含单元格区域的公式，例如sum(A1:A50)或sum(A19:BA19)。我怎样才能用单个单元格替换区域？例如，sum(A1:A50)将变成sum(A1,A2,A3,A4...A49,A50)。我尝试编写一个宏，通过最高引用行下降，在公式中寻找冒号，但很难获得正确的列引用。

如果重要的话，我的所有公式都是嵌套的。这意味着，我可能在 ifferror() 和除法或 Mod 运算符内有 sum()。

有人有什么想法吗，我正在寻找一种新的方法。

谢谢。

答案1

很无聊，但是一个很好的谜题。

Sub rangesplit()
Dim fnlStr As String
Dim strstrt As String
Dim rngsplit() As String
Dim rng As Range
Dim str As Variant
'Change Selection to the range you want. If more than one cell you will need a loop
fnlStr = Selection.Formula
'remove the part before and including the "(" and store in a variable.
strstrt = Left(fnlStr, InStr(fnlStr, "("))
fnlStr = Left(fnlStr, Len(fnlStr) - 1)
fnlStr = Replace(fnlStr, strstrt, "")
'Split the rest on "," in case of multiple ranges.
rngsplit = Split(fnlStr, ",")
'clear fnlstr and start peicing back together
fnlStr = strstrt
'Loop through resultant array
For Each str In rngsplit
    'Check if viable Range
    If Not IsError(Range(str)) Then
        'Loop throug range
        For Each rng In Range(str)
            'Append each address to fnlstr
            fnlStr = fnlStr & rng.Address & ","
        Next rng
    End If
Next str
'remove the extra "," and replace it with ")"
fnlStr = Left(fnlStr, Len(fnlStr) - 1) & ")"
Selection.Formula = fnlStr
End Sub

相关内容