re: vlookup in a msword macro returning an error

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >