Is it possible to create a macro that when a selection of cells is
picked and the macro run a box will appear on the screen which allows
the user to enter a formula which is then applied to all the cells
selected and the new value returned in the original cells?

ie i highlight cells a1, a2, a3... click the macro. A box appears
prompting me to enter a formula of my choosing to apply to the
selection. On clicking OK the formula is applied and the new values
returned to a1, a2, a3

Apologies if this is really simple!!! And thanks in advance for any
help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Try something like the following:

Sub AAA()
Dim F As String
Dim Rng As Range
F = InputBox("Enter A Formula")
If F < "" Then
For Each Rng In Selection.Cells
Rng.Formula = F
Next Rng
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"index" wrote in message
...

Is it possible to create a macro that when a selection of cells
is
picked and the macro run a box will appear on the screen which
allows
the user to enter a formula which is then applied to all the
cells
selected and the new value returned in the original cells?

ie i highlight cells a1, a2, a3... click the macro. A box
appears
prompting me to enter a formula of my choosing to apply to the
selection. On clicking OK the formula is applied and the new
values
returned to a1, a2, a3

Apologies if this is really simple!!! And thanks in advance for
any
help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide
to creating financial statements

Index,

Here's one way. It first prompts for the user to select the range to apply
the formula to, then for the formula. There is no validation on the formula

Sub ApplyFormulae()
Dim rng As Range
Dim sFormula As String

On Error Resume Next
Set rng = Application.InputBox("Please select range to apply formula
to", , , , , , , 8)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "OK, try again later"
Exit Sub
Else
sFormula = InputBox("Please input formula (with leading =)")
If sFormula = "" Then
MsgBox "It's your choice"
Exit Sub
Else
rng.Formula = sFormula
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"index" wrote in message
...

Is it possible to create a macro that when a selection of cells is
picked and the macro run a box will appear on the screen which allows
the user to enter a formula which is then applied to all the cells
selected and the new value returned in the original cells?

ie i highlight cells a1, a2, a3... click the macro. A box appears
prompting me to enter a formula of my choosing to apply to the
selection. On clicking OK the formula is applied and the new values
returned to a1, a2, a3

Apologies if this is really simple!!! And thanks in advance for any
help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Chip and Bob both read you query as wanting the formula put into the
selected cells. I read it differently. Am I the one out of step? It
wouldn't be the first time.
I think that you don't want a formula placed in those cells. Instead,
you want the current cell contents to be one of the arguments of the formula
and then you want the result of the formula put into the same cell. For
instance, if the cell contains 5 and the formula you input is
(CurrentValue*A1) then you would get the result of 5*A1 put into that cell,
not a formula. Is that what you want? HTH Otto
"index" wrote in message
...

Is it possible to create a macro that when a selection of cells is
picked and the macro run a box will appear on the screen which allows
the user to enter a formula which is then applied to all the cells
selected and the new value returned in the original cells?

ie i highlight cells a1, a2, a3... click the macro. A box appears
prompting me to enter a formula of my choosing to apply to the
selection. On clicking OK the formula is applied and the new values
returned to a1, a2, a3

Apologies if this is really simple!!! And thanks in advance for any
help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

You can do this without a macro if you wished.

Select your cells A1:A3. Type a formula in active cell then hit CRTL + ENTER

Gord Dibben XL2002

On Sat, 6 Dec 2003 10:59:32 -0600, index
wrote:


Is it possible to create a macro that when a selection of cells is
picked and the macro run a box will appear on the screen which allows
the user to enter a formula which is then applied to all the cells
selected and the new value returned in the original cells?

ie i highlight cells a1, a2, a3... click the macro. A box appears
prompting me to enter a formula of my choosing to apply to the
selection. On clicking OK the formula is applied and the new values
returned to a1, a2, a3

Apologies if this is really simple!!! And thanks in advance for any
help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements