How in to parse constants in formula to cells
Ron,
I was able to use PapaDos code as to negatives via:
Range("G" & i).Value = IIf(Left(match, 1) = "-", match, match.SubMatches(0))
That said, I am also interested =AVERAGE(1,2,3)
Both you and PapaDos spent a great deal of time for which I am very appreciative.
EagleOne
Ron Rosenfeld wrote:
On Sat, 25 Nov 2006 21:45:02 -0800, PapaDos
wrote:
Something like this ?
Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c
Set range_to_check = [A1:A4] ' SET AS NEEDED
re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub
Nice regex. I was trying to think of something like that but could not.
But it fails on formulas such as:
=AVERAGE(1,2,3)
It also fails to return the negative values, as the OP mentioned in a
subsequent post.
--ron
|