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

#### lovallee

##### Board Regular
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 

'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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

Code:
``````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

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.

Code:
``````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

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

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

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

'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

Replies
3
Views
555
Replies
2
Views
202
Replies
1
Views
148
Replies
1
Views
651
Replies
4
Views
246

### Forum statistics

1,203,171
Messages
6,053,874
Members
444,692
Latest member
Queendom ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back