Formulas that sum or subtract other formulas

JROB 0
Viewed 57

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

I'd 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 could be longer or shorter and include (+ or -).

(='Test Sheet'!E2+'Test Sheet'!E3+'Test Sheet'!E4+'Test Sheet'!E5)
excel-formula split vba excel
2 Answers

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:

enter image description here

Dominique0

Try the following sub.

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
Harun24hr0
Related