PDA

View Full Version : Solved: Return values from formula range



psauber
07-19-2005, 11:54 PM
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

xld
07-20-2005, 03:27 AM
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
07-20-2005, 04:12 AM
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.

xld
07-20-2005, 06:50 AM
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
07-20-2005, 07:46 AM
Brilliant Xld.
May I ask you one other question. I will start a new
thread since the problem is slightly different.