Ron & PapaDos,
After using the different routines, and if I may be so bold:
What works best is that the output should be pure constants used to change cell values and not
"constants" within functions. The reason that I added (A1*3) was to make sure that I could get to
the multiplier "3" [when the OP used parenthesis to make the formula more obvious].
Actually, it would be best for the routine to ignore within-function arguements or settings.
Specifically, any numbers added, subtracted, divided, multiplied
Users have a bad habit of not identifying the pieces-parts of cell values, i.e.
Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc
My plan is to place the output of your routines into separate cells on a new worksheet so that each
constant is completely separated ready to be properly labeled and identified.
Therefore, I need to be able to
1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)
2) Spin any included function or link out to the same worksheet as a string
Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc xxxxxx
'='Sheet1'!A2 999000
Total xxxxxxxxxx
[This total should equal
the displayed value in
the active cell )
I have no references nor information on how to modify/create Regular Expressions
Thanks so much for you time and knowledge
EagleOne
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 e and lsewhere on the w/s?
TIA EagleOne