Users have a tendancy to populate cells with multiple links, each of
which, should be in separate cells to permit an audit trail.

What I am attempting to do is parse those links so as to create that
audit trail.

As I said, my biggest concern was making sure that I was using the
best procedures to ID the formulas.

Thanks for your assistance!

EagleOne

On Apr 16, 3:24 pm, "Don Guillett" wrote:
I worked on the example given. You have to be able to find something that
you know.
Other options would be more complicated and I fail to see why you want to
break it up anyway.
Sounds like a project.

--
Don Guillett
SalesAid Software
"EagleOne" wrote in message

oups.com...



Don,

Thanks for your reply.

In the example that I used, the sheets began similarily. Actually,
the sheets will not most likely be similar.

Later today I'll be able to test your thoughts.

First glance though, it appears that the Operators are hard-coded.

How would the VBA handle ANY Operator in any position? Or in short,
how could I best incorporate the following into your code?

OprSigns = Array("+", "-", "*", "/", "^", "", "<", "<", "=",
"<=")
OprFirst = Len(ActiveCell.Formula)
For N = 0 To 9
OprTemp = InStr(1, ActiveCell.Formula, OprSigns(N),
vbTextCompare)
If OprTemp 0 Then
If OprTemp < OprFirst Then OprFirst = OprTemp
End If
Next

Thanks again Don

EagleOne

On Apr 16, 9:51 am, "Don Guillett" wrote:
You may like this if all start with 3 for the sheet name
Sub ParseFormula()
With Range("b3")
p1 = InStr(.Formula, "3")
p2 = InStr(p1 + 1, .Formula, "3")
.Offset(2) = "='" & Mid(.Formula, p1, p2 - p1 - 2)
p3 = InStr(p2 + 1, .Formula, 3)
.Offset(3) = "='" & Mid(.Formula, p2, p3 - p2 - 2)
p4 = InStr(p3 + 1, .Formula, "+")
.Offset(4) = "='" & Mid(.Formula, p3, p4 - p3)
.Offset(5) = Mid(.Formula, p4 + 1, Len(.Formula) - p4)
'below is ONE line
.Offset(7).Formula = "=(" & .Offset(2) * .Offset(3) & ") / " &
.Offset(4)
& "+" & .Offset(5)
End With
End Sub

--
Don Guillett
SalesAid Software
osoft.com wrote in
message

. ..

2003/2007

Assume B3 formula is:
='3 Period Average EBITDA Summary'!H8*'_
3 Period Average EBITDA Summary'!H22/' _
3 Period Average EBITDA Summary'!H23 _
+5932

What series of VBA procedures would have to best
chance of correctly parsing the three links (or any three others)
so that blank cells in the same column i.e.
Cells(Rows.Count, SameColumn).End(xlUp)(2, 1) would end up

B5 formula ='3 Period Average EBITDA Summary'!H8
B6 formula ='3 Period Average EBITDA Summary'!H22
B7 formula ='3 Period Average EBITDA Summary'!H23
B8 formula = 5932

B10 = ((B5*B6)/B7)+5932

Then B3 is replaced with a link to B10 i.e.:
B3 formula =B10

My biggest concerns are properly identifying the complete formula
strings, the operators and properly handling any constants which may
appear anywhere in the original B3 formula.

Any, I mean ANY, even partial help, very much appreciated

EagleOne- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -