Return values from formula range

[psauber]

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


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

Any help appreciated.

Thanks

[Bob Phillips]

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


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


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

[psauber]

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.

[Bob Phillips]

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



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

[psauber]

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