Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pick out arguments from formula
I need to compare arguments in a set of =sum formulae to find duplicated ones.
Eg i need to find cells with formulae such as =sum( a1;a2;a3;a4;a4) where an argument is duplicated. Is anything in VBA to return the arguments by number from a formula in a cell? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pick out arguments from formula
Not directly. You could use the "split" function and then compare
elements of the array. First, I get the formula and split it to be assigned to the variable "sArguements". This is an array variable that would look like this: sArguements(0)="=sum(a1" sArguements(0)="a2" sArguements(0)="a3" sArguements(0)="a4" sArguements(0)="a4)" Then I strip out the portion of the formula up to the parenthesis and the last parenthesis. Finally, I compare each cell that's referenced by the formula to the other cells referenced by the formula. It could be done more efficiently than this, but I don't know if it would be worthwhile (e.g., if you have a lot of cells in each sum function). You'd have to take another track if you have references that cover more than one cell, (i.e. a1:a3) Public Function test1() As Boolean Dim sFormula As String Dim sArguements Dim i As Integer, x As Integer Dim bDuplicate As Boolean bDuplicate = False sFormula = ActiveSheet.Cells(5, 5).Formula sArguements = Split(sFormula, ",") For i = 0 To UBound(sArguements) If InStr(1, sArguements(i), "(") < 0 Then sArguements(i) = Mid(sArguements(i), InStr(1, sArguements (i), "(") + 1, 999) Else If InStr(1, sArguements(i), ")") < 0 Then sArguements(i) = Left(sArguements(i), InStr(1, sArguements(i), ")") - 1) End If End If Next i For i = 0 To UBound(sArguements) For x = 0 To UBound(sArguements) If Not i = x Then If sArguements(i) = sArguements(x) Then bDuplicate = True End If End If Next x Next i test1 = bDuplicate End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use a formula with 2 arguments | Excel Worksheet Functions | |||
formula pick up formula from another cell automatically | Excel Worksheet Functions | |||
arguments in a formula | Excel Discussion (Misc queries) | |||
Formula to pick out, cut and paste | Excel Worksheet Functions | |||
How do I add more than 30 arguments in a formula | Excel Worksheet Functions |