execute string from a cell

  • Thread starter Thread starter dmonder
  • Start date Start date
D

dmonder

I want to be able to enter a function in one cell as a text string and
be able to use that function in another cell.

For example, I would enter the string "%CELL% - MIN(%RANGE%)" into
B1. The cells B2 - B5 would replace the phrase %CELL% with their
respective cell value and %RANGE% with B2 through the last populated
cell.

I can get Excel to replace %CELL% and %RANGE% but I cannot get it to
execute the resulting string. I used SUBSTITUTE as follows:

=SUBSTITUTE(SUBSTITUTE($B$1,"%CELL%",ADDRESS(ROW(),COLUMN())),"%RANGE
%",ADDRESS(2,COLUMN())&":"&ADDRESS(2+COUNTA(A:A),COLUMN()))

I get this:

Function:,'Sheet 1'!%CELL% - MIN('Sheet 1'!%RANGE%)
Program 1,='Sheet 1'!$B$2 - MIN('Sheet 1'!$B$2:$B$5)
Program 2,='Sheet 1'!$B$3 - MIN('Sheet 1'!$B$2:$B$5)
Program 3,='Sheet 1'!$B$4 - MIN('Sheet 1'!$B$2:$B$5)
Program 4,='Sheet 1'!$B$5 - MIN('Sheet 1'!$B$2:$B$5)

They may look like formula's in B2 - B5 but they are text strings.
How can I now execute them?

David
 
Take a look at the INDIRECT function. I think that will give you what you
are looking for.
 
You need a user-defined function like this:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

and then use it like this:

=eval(B2)

Hope this helps.

Pete
 
You need a user-defined function like this:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

and then use it like this:

=eval(B2)

Hope this helps.

Pete
Click to expand...

Is there a way to do this without a user defined function? The
problem is that this worksheet will be used by people that may find it
uncomfortable to get an prompt to allow a script to run. If I can get
this to work as a cell function, then that prompt will not come up.

INDIRECT does not work. I get a #REF! error.

David
 
INDIRECT won't work for this - you can build up cell and range
references from strings using it, but you can't execute a string that
looks like a function with it.

I don't know of a non-VBA way that will help you to do what you
want ...

.... but others might!

Pete
 
How about Text to Columns?

If it is desired to convert the string into a formula rather than into a
value, then if A1 contains:
=1+2 as a string will be converted into the formula
=1+2 in either the same cell or a different cell.
 
How about Text to Columns?

If it is desired to convert the string into a formula rather than into a
value, then if A1 contains:
=1+2 as a string will be converted into the formula
=1+2 in either the same cell or a different cell.
Click to expand...

I don't understand what you mean? Can you give more details?

David
 
Are you copying the Substitute formula to itself and then paste special as
"Values" first?
Then TTC.
 
Back
Top