Problem with C.Formula = "=(" & C.Formula &amp

[lovallee]

Hi,

I am trying to build a short macro that will add a multiplying factor to a cell formula while preserving the original formula between parentesis.
Example:
MyFactor = 1.5
Cell formula: =123+500/45 needs to become =(123+500/45)*1.5

I came up with the following code which give me: Run time error [1004]


Sub AddFactorToFormula()
'Add a multiplication factor in the formula to a range of cell

Dim C As Range

MyFactor = InputBox(Prompt:="Enter numeric factor")

For Each C In Selection

If Not IsEmpty(C) Then
C.Formula = "=(" & C.Formula & ")*" & MyFactor
End If
Next C

End Sub

Anyone knows the problem?

Thanks!

Louis

 

[Cbrine]

Re: Problem with C.Formula = "=(" & C.Formula

You didn't define what type Factor is, I believe the variable is being assigned to a string type. You should always define your types, otherwise strange things will happen. Maybe add the option explict to force you.

Sub AddFactorToFormula() 
'Add a multiplication factor in the formula to a range of cell 

Dim C As Range, Factor as integer

MyFactor = InputBox(Prompt:="Enter numeric factor") 

For Each C In Selection 

If Not IsEmpty(C) Then 
C.Formula = "=(" & C.Formula & ")*" & MyFactor 
End If 
Next C 

End Sub

HTH
Cal

 

[Richie(UK)]

Re: Problem with C.Formula = "=(" & C.Formula

Hi Louis,

If you use a messagebox to display the formula that you are trying to use you will see that you have two lots of equals (=) signs.

Try the following instead:

Sub AddFactorToFormula()
'Add a multiplication factor in the formula to a range of cell

    Dim C As Range, MyFactor As Double
    
    MyFactor = InputBox(Prompt:="Enter numeric factor")
    
    For Each C In Selection.SpecialCells(xlCellTypeFormulas)
        C.Formula = "=(" & Mid(C.Formula, 2, 999) & ")*" & MyFactor
    Next C

End Sub

HTH

 

[lovallee]

Re: Problem with C.Formula = "=(" & C.Formula

Thanks Richie, your code solved my problem!

Effectively, the Mid(C.Formula, 2, 999) got rid of the initial formula = sign...


Louis

 

[lovallee]

Re: Problem with C.Formula = "=(" & C.Formula

Problem solved....I added another IF level so that direct entry numeric cells also gets converted

Sub AddFactorToFormula()
'Add a multiplication factor in the formula to a range of cell

Dim C As Range, MyFactor As Double

MyFactor = InputBox(Prompt:="Enter numeric factor")

For Each C In Selection
If C.HasFormula Then
C.Formula = "=(" & Mid(C.Formula, 2, 999) & ")*" & MyFactor

Else
If IsNumeric(C) Then
C.Formula = "=(" & C.Formula & ")*" & MyFactor
End If
End If
Next C

End Sub