formulas that sum or subtract other formulas-CodePudding

Hi I have the following formulas that sum or subtract other formulas, for example:

='Test Sheet'!E2 'Test Sheet'!E3 'Test Sheet'!E4 'Test Sheet'!E5

Id like to write a macro that separates each item and introduces it in the row below. So if the formula above was in cell C1 the result would be:

C1: ='Test Sheet'!E2
C2:  'Test Sheet'!E3
C3:  'Test Sheet'!E4
C4:  'Test Sheet'!E5

The initial formula

(='Test Sheet'!E2 'Test Sheet'!E3 'Test Sheet'!E4 'Test Sheet'!E5)

could be longer or shorter and include ( or -).

Any thoughts on this?

Thanks!

CodePudding user response：

Are you aware that you can split() a string, like in the following example:

Sub test()
Dim a As String
a = "1 2 3"
b = Split(a, " ")
End Sub

When looking at the watch Window, b looks as follows:

CodePudding user response：

Try below sub.

Sub SplitFormula()
Dim arr As Variant
Dim i As Integer

arr = Split(Sheets("Test Sheet").Range("C1").Formula, " ")

    For i = LBound(arr) To UBound(arr)
        Sheets("Test Sheet").Range("C" & 1   i).Formula = "='Test Sheet'!E" & i   2
    Next i

End Sub

Edit after comment.

Sub SplitFormula()
Dim arr As Variant
Dim i As Integer
    arr = Split(Sheets("Sheet ABC").Range("C1").Formula, " ")
        For i = LBound(arr) To UBound(arr)
            Sheets("Sheet ABC").Range("C" & 1   i).Formula = "='Test Sheet'!E" & i   2
        Next i
End Sub