# Using range value as formula in For Each loop

• ### Question

• I've searched around but can't seem to find a good way to do this... Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop... What I'm trying to do is for each chart in a worksheet, I want to move the Rotation (x) and the Elevation (Y) along a path using that formula. Using this formula as an example, it would move the first chart at a rotation of 1 and an elevation of -8, then the next chart would move/rotate 2 and -2, the next chart 3 and 8, etc. This code does nothing at all, but I thought it might at least provide a starting point. I cannot figure out how to get the range Value of "2x^2-10" to be a formula and not a string. Any thoughts would be appreciated!

'Dim strArc As
Dim x As Double
'Dim y As Double
Dim Shape As ChartObject

For Each Shape In ActiveSheet.ChartObjects
x = x + 1
strArc = (Range("Arc").Formula)
y = strArc  '2 * x ^ 2 - 10
Shape.Activate
strChartName = Shape.Name

With ActiveSheet.ChartObjects(strChartName).Chart
.Rotation = .Rotation + x
.Elevation = .Elevation + y
DoEvents
End With

Next Shape

Wednesday, March 2, 2016 7:15 PM

• Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop...

To get a formula as string, modify and evaluate it leads to tons of code, because you have to check syntax errors, etc. etc.

A simple and easy way is to use named cells and a real formula. As you can see here I've named cell B2 as "x" and C2 as "y":

It doesn't matter which cells you use, the benefit of named cells is that you can use the names in your code:

```Sub Test()
Dim CO As ChartObject

For Each CO In ActiveSheet.ChartObjects
Range("x") = Range("x") + 1
With CO.Chart
.Rotation = .Rotation + Range("x")
.Elevation = .Elevation + Range("y")
End With
Next
End Sub```

Andreas.

• Proposed as answer by Tuesday, March 8, 2016 1:10 PM
• Marked as answer by Tuesday, March 8, 2016 5:07 PM
Thursday, March 3, 2016 11:05 AM

### All replies

• Hi, REmmons88

According to your description, please correct me if I have any misunderstandings on your question, it seems that you want to convert this "string formula" into a real formula and calculate its value, so I suggest that you could refer to below code:

```Sub ConvertFomular()
Dim strFomular As String
x = 10
strFomular = "2 *" & x & "^ 2 - 10"
Debug.Print Eval(strFomular)
End Sub

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
```

• Proposed as answer by Tuesday, March 8, 2016 1:10 PM
Thursday, March 3, 2016 7:14 AM
• Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop...

To get a formula as string, modify and evaluate it leads to tons of code, because you have to check syntax errors, etc. etc.

A simple and easy way is to use named cells and a real formula. As you can see here I've named cell B2 as "x" and C2 as "y":

It doesn't matter which cells you use, the benefit of named cells is that you can use the names in your code:

```Sub Test()
Dim CO As ChartObject

For Each CO In ActiveSheet.ChartObjects
Range("x") = Range("x") + 1
With CO.Chart
.Rotation = .Rotation + Range("x")
.Elevation = .Elevation + Range("y")
End With
Next
End Sub```

Andreas.

• Proposed as answer by Tuesday, March 8, 2016 1:10 PM
• Marked as answer by Tuesday, March 8, 2016 5:07 PM
Thursday, March 3, 2016 11:05 AM
• This worked beautifully, thank you so much!  For the sake of my users, I wanted them to see the formula in the cell they're using rather then the value so I also added a function that would take whatever they type (for example: x*2) and turn it into a formula in a hidden cell and named that cell y.  And with that change, I got exactly what I was looking for!  Again, thanks for your help!
Tuesday, March 8, 2016 5:11 PM