Evaluate Method Returns Type Mismatch

[todtown]

This is so strange I'm not sure how to ask.

I have a procedure that opens each workbook in a given folder. With
each workbook some code is applied. The workbook object and excel
instance are destroyed and then the loop goes to the next workbook,
etc.

I have this statement that is applied to each workbook:

CurrentRow = Evaluate("=MATCH(""" & Cell.Value & """,Survey_Questions,
0)+ROW(Survey_Questions)-1")

'Cell.Value is the string "Phone Support"
'Survey_Questions is a named range that contains as list of string
values, including "Phone Support"

So the formula would read:
=MATCH("Phone Support",Survey_Questions,0)+ROW(Survey_Questions)-1

This formula returns the row where the Cell.Value is located. However,
the statement produces a type mismatch error. If I run just the
formula in the Immediate window and then paste that into the worksheet
it works.

Why am I getting the type mismatch error.

Also, I know there are other ways to get the row. This is just an
example.

tod

 

[Guest]

You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)

 

[todtown]

After some more testing I can exlain the problem better (I think).

The error does not occur with the first workbook, but the second. It
returns error 2029 (#Name) with the named ranges, but error 2023
(#Ref) if I replace the named ranges in the formula with the actual
address. I "think" with the second workbook, the code in the statement
is still trying to reference the first workbook. I have the same names
in each of the workbooks.

Even though I empty all my global variables and destroy all of my
objects before cycling to the next workbook, I think something inside
the evaluate method is still referencing the previous workbook.

So... if my theory is correct, how can I get the code to look at the
newly-opened workbook instead of the one that was just closed?


tod

 

[todtown]

After some more testing I can exlain the problem better (I think).

The error does not occur with the first workbook, but the second. It
returns error 2029 (#Name) with the named ranges, but error 2023
(#Ref) if I replace the named ranges in the formula with the actual
address. I "think" with the second workbook, the code in the statement
is still trying to reference the first workbook. I have the same names
in each of the workbooks.

Even though I empty all my global variables and destroy all of my
objects before cycling to the next workbook, I think something inside
the evaluate method is still referencing the previous workbook.

So... if my theory is correct, how can I get the code to look at the
newly-opened workbook instead of the one that was just closed?


tod

 

[todtown]

After some more testing I can exlain the problem better (I think).

The error does not occur with the first workbook, but the second. It
returns error 2029 (#Name) with the named ranges, but error 2023
(#Ref) if I replace the named ranges in the formula with the actual
address. I "think" with the second workbook, the code in the statement
is still trying to reference the first workbook. I have the same names
in each of the workbooks.

Even though I empty all my global variables and destroy all of my
objects before cycling to the next workbook, I think something inside
the evaluate method is still referencing the previous workbook.

So... if my theory is correct, how can I get the code to look at the
newly-opened workbook instead of the one that was just closed?


tod

 

[Guest]

Tod, can you post more of your code? Also as a general means of debugging,
have you paused when the error occurs and used the immediate window to see
what all the objects return?

 

[Charles Williams]

Sound like your problem is unqualified references (which default to the
active sheet).
Try either expanding all the references to include the workbook and
worksheet or using Worksheet.Evaluate rather than Application.Evaluate,
which will then force the unqualified references to resolve to whatever
Worksheet you reference in Worksheet.Evaluate.

There are some more quirks of the Evaluate method listed at
http://www.decisionmodels.com/calcsecretsh.htm

HTH
Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

 

[todtown]

Sound like your problem is unqualified references (which default to the
active sheet).

That was it!! I simply put my Excel object variable in front of
Evaluate, like:

objXL.Evaluate.......

and that took care of it.

thanx,
tod

 

[todtown]

Sound like your problem is unqualified references (which default to the
active sheet).

That was it!! I simply put my Excel object variable in front of
Evaluate, like:

objXL.Evaluate.......

and that took care of it.

thanx,
tod

 

[todtown]

Sound like your problem is unqualified references (which default to the
active sheet).

That was it!! I simply put my Excel object variable in front of
Evaluate, like:

objXL.Evaluate.......

and that took care of it.

thanx,
tod