I am doing a vlookup in a word macro and I keep getting an error as the value
returned which is error 2042. I am unsure what exactly that error is and I
capture it using the iserror method but what I would really like is the
actual value from the spreadsheet I am doing the lookup on. A sample of my
code is below:

AppXL.workbooks.Open ("C:\Model Pilot\Listing" & dlrrep & ".xls")
AppXL.workbooks.Open ("C:\Model Pilot\Model Grid_test.xls")
AppXL.workbooks.Open ("C:\Model Pilot\Names.xls")


workbooks("Listing" & dlrrep & ".xls").worksheets("Sheet1").Activate
With Activesheet
'get the last row of data for the ranges.
lislrow = AppXL.Cells(Rows.Count, "A").End(xlUp).Row
For counter = 2 To lislrow
lokval = AppXL.Cells(counter, 15).Value
'row Z - col L of model grid
res = AppXL.vlookup(lokval, workbooks("Model
Grid_test.xls").Sheets("Sheet1").Range("A2:M55"), 12, False)
If IsError(res) Then
AppXL.Cells(counter, 26).Value = ""
Else:
xlApp.Cells(counter, 26).Value = res
End If
next counter

The value it is supposed to return is "scs". Any help is appreciated even
if I knew what the error 2042 is I could figure out what might be my problem.
If I do it from excel in the spreadsheet it doesn't have any errors so I'm a
little stumped.
TIA
Heather

? cvErr(xlErrNa)
Error 2042

so it is the #N/A error meaning the value was not found.

--
Regards,
Tom Ogilvy



"HeatherO" wrote in message
...
I am doing a vlookup in a word macro and I keep getting an error as the
value
returned which is error 2042. I am unsure what exactly that error is and
I
capture it using the iserror method but what I would really like is the
actual value from the spreadsheet I am doing the lookup on. A sample of
my
code is below:

AppXL.workbooks.Open ("C:\Model Pilot\Listing" & dlrrep & ".xls")
AppXL.workbooks.Open ("C:\Model Pilot\Model Grid_test.xls")
AppXL.workbooks.Open ("C:\Model Pilot\Names.xls")


workbooks("Listing" & dlrrep & ".xls").worksheets("Sheet1").Activate
With Activesheet
'get the last row of data for the ranges.
lislrow = AppXL.Cells(Rows.Count, "A").End(xlUp).Row
For counter = 2 To lislrow
lokval = AppXL.Cells(counter, 15).Value
'row Z - col L of model grid
res = AppXL.vlookup(lokval, workbooks("Model
Grid_test.xls").Sheets("Sheet1").Range("A2:M55"), 12, False)
If IsError(res) Then
AppXL.Cells(counter, 26).Value = ""
Else:
xlApp.Cells(counter, 26).Value = res
End If
next counter

The value it is supposed to return is "scs". Any help is appreciated even
if I knew what the error 2042 is I could figure out what might be my
problem.
If I do it from excel in the spreadsheet it doesn't have any errors so
I'm a
little stumped.
TIA
Heather

You mean it did not find my lookup value or the one to return? If it is the
lookup value I am just wondering if it is because I am doing a lookup from
one number to another number. If I check the formating of both the lookup
value and the one I am looking for they are both formated as numbers but I
didn't think that would give me this error. Could it have anything to do
with page protection? It's just wierd that when I do it in the spreadsheet
it's fine but doing it from the macro and it's not.
TIA
Heather

"Tom Ogilvy" wrote:

? cvErr(xlErrNa)
Error 2042

so it is the #N/A error meaning the value was not found.

--
Regards,
Tom Ogilvy



"HeatherO" wrote in message
...
I am doing a vlookup in a word macro and I keep getting an error as the
value
returned which is error 2042. I am unsure what exactly that error is and
I
capture it using the iserror method but what I would really like is the
actual value from the spreadsheet I am doing the lookup on. A sample of
my
code is below:

AppXL.workbooks.Open ("C:\Model Pilot\Listing" & dlrrep & ".xls")
AppXL.workbooks.Open ("C:\Model Pilot\Model Grid_test.xls")
AppXL.workbooks.Open ("C:\Model Pilot\Names.xls")


workbooks("Listing" & dlrrep & ".xls").worksheets("Sheet1").Activate
With Activesheet
'get the last row of data for the ranges.
lislrow = AppXL.Cells(Rows.Count, "A").End(xlUp).Row
For counter = 2 To lislrow
lokval = AppXL.Cells(counter, 15).Value
'row Z - col L of model grid
res = AppXL.vlookup(lokval, workbooks("Model
Grid_test.xls").Sheets("Sheet1").Range("A2:M55"), 12, False)
If IsError(res) Then
AppXL.Cells(counter, 26).Value = ""
Else:
xlApp.Cells(counter, 26).Value = res
End If
next counter

The value it is supposed to return is "scs". Any help is appreciated even
if I knew what the error 2042 is I could figure out what might be my
problem.
If I do it from excel in the spreadsheet it doesn't have any errors so
I'm a
little stumped.
TIA
Heather

Hi Tom,
Never mind I figured it out, I guess the tylenol finally kicked in. I had
the lokval defined as a string and since it was a numeric value I changed it
to be defined as an integer and it worked fine. It's hard to think clearly
when my head feels like a major brain freeze. Thanks for your help, I really
appreciated it, it helped me figure out where to begin.
Heather

"HeatherO" wrote:

You mean it did not find my lookup value or the one to return? If it is the
lookup value I am just wondering if it is because I am doing a lookup from
one number to another number. If I check the formating of both the lookup
value and the one I am looking for they are both formated as numbers but I
didn't think that would give me this error. Could it have anything to do
with page protection? It's just wierd that when I do it in the spreadsheet
it's fine but doing it from the macro and it's not.
TIA
Heather

"Tom Ogilvy" wrote:

? cvErr(xlErrNa)
Error 2042

so it is the #N/A error meaning the value was not found.

--
Regards,
Tom Ogilvy



"HeatherO" wrote in message
...
I am doing a vlookup in a word macro and I keep getting an error as the
value
returned which is error 2042. I am unsure what exactly that error is and
I
capture it using the iserror method but what I would really like is the
actual value from the spreadsheet I am doing the lookup on. A sample of
my
code is below:

AppXL.workbooks.Open ("C:\Model Pilot\Listing" & dlrrep & ".xls")
AppXL.workbooks.Open ("C:\Model Pilot\Model Grid_test.xls")
AppXL.workbooks.Open ("C:\Model Pilot\Names.xls")


workbooks("Listing" & dlrrep & ".xls").worksheets("Sheet1").Activate
With Activesheet
'get the last row of data for the ranges.
lislrow = AppXL.Cells(Rows.Count, "A").End(xlUp).Row
For counter = 2 To lislrow
lokval = AppXL.Cells(counter, 15).Value
'row Z - col L of model grid
res = AppXL.vlookup(lokval, workbooks("Model
Grid_test.xls").Sheets("Sheet1").Range("A2:M55"), 12, False)
If IsError(res) Then
AppXL.Cells(counter, 26).Value = ""
Else:
xlApp.Cells(counter, 26).Value = res
End If
next counter

The value it is supposed to return is "scs". Any help is appreciated even
if I knew what the error 2042 is I could figure out what might be my
problem.
If I do it from excel in the spreadsheet it doesn't have any errors so
I'm a
little stumped.
TIA
Heather