Directly setting variable to formula
 

Hi everyone -

I'm trying to figure out a more direct way of assigning a value that is
derived from a formula to a variable rather than having to place the
formula on the worksheet first.

Example:

I have a text item in cell A1 on a worksheet. I'm extracting a portion
of that text. Right now I'm doing this by assigning another cell to
this value using:

ActiveCell.FormulaR1C1 = "=LEFT(R1C1,FIND(""Accounts"",R1C1)-2)"
tVal = ActiveCell.Value

Is there someway to skip assigning a cell this formula and move the
value directly into tVal?

As a follow-up. Is there a quick way to load that tVal into each
location of a 1-D array of length n?

Thanks in advance

Marston

Directly setting variable to formula
 

Try

Evaluate("=LEFT(A1,FIND(""Accounts"",A1)-2)")

--

HTH

RP
(remove nothere from the email address if mailing direct)


" wrote in message
ups.com...
Hi everyone -

I'm trying to figure out a more direct way of assigning a value that is
derived from a formula to a variable rather than having to place the
formula on the worksheet first.

Example:

I have a text item in cell A1 on a worksheet. I'm extracting a portion
of that text. Right now I'm doing this by assigning another cell to
this value using:

ActiveCell.FormulaR1C1 = "=LEFT(R1C1,FIND(""Accounts"",R1C1)-2)"
tVal = ActiveCell.Value

Is there someway to skip assigning a cell this formula and move the
value directly into tVal?

As a follow-up. Is there a quick way to load that tVal into each
location of a 1-D array of length n?

Thanks in advance

Marston