| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to parse multiple links to one cell
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA to parse multiple links to one cell
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 - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA to parse multiple links to one cell
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA to parse multiple links to one cell
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA to parse multiple links to one cell
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| parse comma separated text to multiple cells and remove duplicates | Excel Worksheet Functions | |||
| HOW TO | *automatically* parse comma separated text to multiple ce | Excel Discussion (Misc queries) | |||
| Links (multiple) to Imbedded W/S in adjacent cell | Excel Discussion (Misc queries) | |||
| Parse cell contents ? | Excel Discussion (Misc queries) | |||
| Maintain cell links when renaming directory containing multiple f | Excel Worksheet Functions | |||
Hybrid Mode
