Hello,

Can anyone tell me how to create a macro that when a range of cells are
selected and the macro run will prompt the user to enter a formula into
a box that appears on screen and then when the user presses ok the
formula entered is applied to the selected cells?

Thanks very much


---
Message posted from http://www.ExcelForum.com/

Try this, but it doesn't check for validity of formula


Sub myFormlae()
Dim sformula
sformula = InputBox("Supply formula (inlclude the leading =)")
If Not IsEmpty(sformula) Then
Selection.Formula = sformula
End If
End Sub


--

HTH

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

"ali" wrote in message
...
Hello,

Can anyone tell me how to create a macro that when a range of cells are
selected and the macro run will prompt the user to enter a formula into
a box that appears on screen and then when the user presses ok the
formula entered is applied to the selected cells?

Thanks very much


---
Message posted from http://www.ExcelForum.com/

Thanks but there is a problem with the line
"Selection.Formula = sformula"
which results is an application defined or object defined error

Any ideas?


---
Message posted from http://www.ExcelForum.com/

Sub myFormula()
Dim sformula as String
sformula = InputBox("Supply formula (inlclude the leading =)")
If Not IsEmpty(sformula) Then
sFormula = Trim(sFormula)
if left(sFormula,1) < "=" then _
sFormula = "=" & sFormula
On Error Resume Next
Selection.Formula = sformula
On Error goto 0
if err.Number < 0 then
msgbox "Invalid formula
err.clear
end if
End If
End Sub

--
Regards,
Tom Ogilvy

ian123 wrote in message
...
Thanks but there is a problem with the line
"Selection.Formula = sformula"
which results is an application defined or object defined error

Any ideas?


---
Message posted from http://www.ExcelForum.com/

Apologies if i am missing something really obvious here but i cant get
this to workas desired.

Imagine i have the values 1,2,3,4,5 in cells a1:a5. I want to run the
macro, enter "=*2" in the input box and have the values 2,4,6,8,10
replace 1,2,3,4,5 in cells a1:a5

However at present i'm running it and having "*2" returned in a1:a5 -
am i making a fundamental error at the input stage or is it something
else?

Thanks again


---
Message posted from http://www.ExcelForum.com/

What did you enter as the formula? I'm betting that something was wrong with
it. And excel would have yelled if you did it manually.

Depending on the formula, this might head off some errors:

Option Explicit
Sub myFormlae()
Dim sformula As Variant
sformula = InputBox("Supply formula (inlclude the leading =)")
If Not IsEmpty(sformula) Then
If Left(sformula, 1) < "=" Then
sformula = "=" & sformula
End If
If IsError(Evaluate(sformula)) Then
MsgBox "Hey, that can't be right"
Exit Sub
Else
Selection.Formula = sformula
End If
End If
End Sub

ian123 wrote:

Thanks but there is a problem with the line
"Selection.Formula = sformula"
which results is an application defined or object defined error

Any ideas?

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

I'm entering "=+1" into the input box (for example). This simply
returns the value 1 to the selected cell rather than apply +1 to the
value already in the cell. Any ideas where i'm going wrong?


---
Message posted from http://www.ExcelForum.com/

Bob wrote the macro as if you were typing the complete formula into the
cell--not modifying an existing value/formula.

If you put 2 in an unused cell and edit|copy, then you can select A1:A5 and
edit|paste special|and check multiply.

You won't end up with a formula, but it's pretty easy.

Do you really want a macro/formula solution?

Option Explicit
Sub myFormlae2()

Dim myRng As Range
Dim myCell As Range
Dim myVal As Variant

myVal = Application.InputBox(Prompt:="Enter a number", Type:=1)
If myVal = False Then
Exit Sub 'user hit cancel
End If

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
If .HasFormula Then
.Formula = "=(" & Mid(.Formula, 2) & ")*" & myVal
Else
.Formula = "=" & .Formula & "*" & myVal
End If
End With
Next myCell

End Sub

This'll have trouble if the cell isn't numeric.



ian123 wrote:

Apologies if i am missing something really obvious here but i cant get
this to workas desired.

Imagine i have the values 1,2,3,4,5 in cells a1:a5. I want to run the
macro, enter "=*2" in the input box and have the values 2,4,6,8,10
replace 1,2,3,4,5 in cells a1:a5

However at present i'm running it and having "*2" returned in a1:a5 -
am i making a fundamental error at the input stage or is it something
else?

Thanks again

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

I didn't notice that you wanted to pass the operand, too

Option Explicit
Sub myFormlae2()

Dim myRng As Range
Dim myCell As Range
Dim myVal As String

myVal = InputBox(Prompt:="Enter a String")
If Trim(myVal) = "" Then
Exit Sub 'user hit cancel
End If

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
If .HasFormula Then
.Formula = "=(" & Mid(.Formula, 2) & ")" & myVal
Else
.Formula = "=" & .Formula & myVal
End If
End With
Next myCell

End Sub

Don't put the equal sign.

ian123 wrote:

I'm entering "=+1" into the input box (for example). This simply
returns the value 1 to the selected cell rather than apply +1 to the
value already in the cell. Any ideas where i'm going wrong?

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson