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

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

"A good example is twice the value of good advice."


"todtown" wrote:

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

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



On Nov 20, 2:31 pm, Charles Chickering
wrote:
You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)
--
Charles Chickering

"A good example is twice the value of good advice."



"todtown" wrote:
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- Hide quoted text -

- Show quoted text -

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



On Nov 20, 2:31 pm, Charles Chickering
wrote:
You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)
--
Charles Chickering

"A good example is twice the value of good advice."



"todtown" wrote:
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- Hide quoted text -

- Show quoted text -

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



On Nov 20, 2:31 pm, Charles Chickering
wrote:
You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)
--
Charles Chickering

"A good example is twice the value of good advice."



"todtown" wrote:
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- Hide quoted text -

- Show quoted text -

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 Chickering

"A good example is twice the value of good advice."


"todtown" wrote:

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



On Nov 20, 2:31 pm, Charles Chickering
wrote:
You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)
--
Charles Chickering

"A good example is twice the value of good advice."



"todtown" wrote:
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- Hide quoted text -

- Show quoted text -

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" wrote in message
...
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



On Nov 20, 2:31 pm, Charles Chickering
wrote:
You missed a " in your """. It should be """" & Cell.Value & """". If in
doubt try using the character command:
Chr(34) & Cell.Value & Chr(34)
--
Charles Chickering

"A good example is twice the value of good advice."



"todtown" wrote:
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- Hide quoted text -

- Show quoted text -

On Nov 20, 4:13 pm, "Charles Williams"
wrote:
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

On Nov 20, 4:13 pm, "Charles Williams"
wrote:
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

On Nov 20, 4:13 pm, "Charles Williams"
wrote:
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