Help with Eval function

[Guest]

Hi all-

I've got an Eval function defined as:
Function Eval(scell As String)
Application.Volatile
Eval = Application.Evaluate(scell)
End Function

I can't get Eval to evaluate the String as a formula as desired. Here's a mocked up example:

H4: LOWER(K&ROW())
H5: SQRT(K&ROW())

K15: AbcD
K16: 49

L15: =Eval(H4)
L16: =Eval(H5)

I would expect the value in L15 to be "abcd" and L16 to be 7. Instead, I get the infamous #NAME? in my face. What gives?

-brandon

 

[Bob Phillips]

Brandon,

That is because the ROW() is a worksheet function, and when you pass it as
part of a string to the function, it cannot evaluate it. You need to evalue
ROW when passing, like so

K4: LOWER(K
L15: =eval(H4&ROW(H4)&")")

But you can do it directly with INDIRECT, with

=LOWER(INDIRECT("K"&ROW(H4)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi all-

I've got an Eval function defined as:
Function Eval(scell As String)
Application.Volatile
Eval = Application.Evaluate(scell)
End Function

I can't get Eval to evaluate the String as a formula as desired. Here's a mocked up example:

H4: LOWER(K&ROW())
H5: SQRT(K&ROW())

K15: AbcD
K16: 49

L15: =Eval(H4)
L16: =Eval(H5)

I would expect the value in L15 to be "abcd" and L16 to be 7. Instead, I

get the infamous #NAME? in my face. What gives?