Return values from formula range

I need to return the values from the range shown below
and not just the formulas.

Code:

---

objExcel.Range("A1:BO1").FormulaR1C1 = "=RIGHT(R[1]C,10)"

---

Any help appreciated.

Thanks

Quote:

---

Originally Posted by psauber

I need to return the values from the range shown below
and not just the formulas.

Code:

---

objExcel.Range("A1:BO1").FormulaR1C1 = "=RIGHT(R[1]C,10)"

---

---

What exactly are you trying to achieve?

The code you give sets the formula in those cells, it does not return them.

To get a value, you would just read it

Code:

---

myVar =  objExcel.Range("A1").Value

---

Thanks for your reply.
The code returns what is in row 2 ie. RIGHT(R[1]C,10)
but as a formula. What I want is the absolute value of row 2 not the formula
but I only want the 10 characters from the right as shown above.
I hope I have clarified sufficiently.

Quote:

---

Originally Posted by psauber

The code returns what is in row 2 ie. RIGHT(R[1]C,10)
but as a formula. What I want is the absolute value of row 2 not the formula
but I only want the 10 characters from the right as shown above.
I hope I have clarified sufficiently.

---

I am sorry, but that is not what the code does.

The code inserts a formula in all of the cells A1:BO1. That formula returns the right-most 10 characters of the cell below. So if a cell a2 contains "THis is a very long text", A1 will show " long text".

Just occurred to me that you might mean that you want create the value in those cells, not the formula, if so, try this

Code:

---

With objExcel.Range("A1:BO1")
        .FormulaR1C1 = "=RIGHT(R[1]C,10)"
        .Value = .Value
    End With

---

Brilliant Xld.
May I ask you one other question. I will start a new
thread since the problem is slightly different.