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