How in to parse constants in formula to cells
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
--
Regards,
Luc.
"Festina Lente"
" wrote:
2003
If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10
-or-
=Round(A1*0.035) ' Need the.35 parsed to Z11
-or-
=P39/$C40*12-P39 'Need the "12" parsed to Z13
-or-
=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15
I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?
TIA EagleOne
|