Seperating a formula into segments

Posted by David Hinchee on October 19, 2001 8:43 AM

I'm a VBA beginner, and I need a macro that can seperate parts of a formula and put them in different cells. For example, if I have this formula in cell A1:

A1: =1000 + 1200/2 + 1800/3

I'd like end up with the following cells & formulas:

No change to the original formula:
A1: =1000 + 1200/2 + 1800/3

Add formulas to these cells as shown:
B1: =1000
C1: =1200/2
D1: =1800/3

In my application, the segments of the original formulas are always seperated by the + operator, and the original formula usually has from one to three segments.

Thanks for the help!

David

Posted by Barrie Davidson on October 19, 2001 9:12 AM

David, this code will break out up to three parts of a formula for the cells selected in a column (you can only select one column). I assumed you will not have four parts.

Sub Extract_Formulas()
' Written by Barrie Davidson
Dim formula1, formula2, formula3
Dim position1, position2, position3 As Integer

On Error Resume Next
For Each cell In Selection
position1 = InStr(cell.Formula, "+")
position2 = InStr(position1 + 1, cell.Formula, "+")
position3 = InStr(position2 + 1, cell.Formula, "+")
formula1 = Mid(cell.Formula, 1, position1 - 1)
If position1 = 0 Then
formula1 = cell.Formula
formula2 = 0
formula3 = 0
Else
If position2 = 0 Then
formula2 = "=" & Mid(cell.Formula, position1 + 1, Len(cell.Formula) - position2 - position1)
formula3 = 0
Else
formula2 = "=" & Mid(cell.Formula, position1 + 1, position2 - position1 - 1)
formula3 = "=" & Mid(cell.Formula, position2 + 1, Len(cell.Formula) - position3 - position2)
End If
End If
cell.Offset(0, 1).Formula = formula1
cell.Offset(0, 2).Formula = formula2
cell.Offset(0, 3).Formula = formula3
Next cell

End Sub

Hope this works for you.

BarrieBarrie Davidson