I have three workbooks - one from which I'm running by VBA code, one workbook
with data and a third workbook with a table of charges. I want to use the
value of a program code field from the database and find that same value in
the table of charges. For instance, one value from the database program code
field is ASC. I want to look for that value in the first column of the table
of charges and return that value, if it's found. Both fields are set to text
using the format cells function. The table is sorted by ascending program
codes.

In VBA, my code is as follows:

Dim oCell as Object
Dim tProgCode as String
Dim tLookup as Variant
Dim tStudentCharges as Range

tProgCode = oCell.Offset(0,2).Value
Set tStudentCharges = Workbooks(fChargesName).Worksheets("Sheet1"). _
Range(Application.Names("SPTable"))
tLookup = Application.VLookup(tProgCode, tStudentCharges,1,False)

I have checked to see that the tProgCode is pulling the correct field. I
have also checked to make sure the table is being correctly identified with
the Set tStudentCharges line. However, tLookup only evaluates to Error 2042,
even though the tProgCode can be found in the first column of the table. Any
ideas on why this isn't working?
Thanks,

I think your lookup table assignment is not correct, try.....

Set tStudentCharges =
Workbooks("fChargesName").Worksheets("Sheet1").Ran ge("SPTable")


--
Cheers
Nigel



"LoriL" wrote in message
...
I have three workbooks - one from which I'm running by VBA code, one
workbook
with data and a third workbook with a table of charges. I want to use the
value of a program code field from the database and find that same value
in
the table of charges. For instance, one value from the database program
code
field is ASC. I want to look for that value in the first column of the
table
of charges and return that value, if it's found. Both fields are set to
text
using the format cells function. The table is sorted by ascending program
codes.

In VBA, my code is as follows:

Dim oCell as Object
Dim tProgCode as String
Dim tLookup as Variant
Dim tStudentCharges as Range

tProgCode = oCell.Offset(0,2).Value
Set tStudentCharges = Workbooks(fChargesName).Worksheets("Sheet1"). _
Range(Application.Names("SPTable"))
tLookup = Application.VLookup(tProgCode, tStudentCharges,1,False)

I have checked to see that the tProgCode is pulling the correct field. I
have also checked to make sure the table is being correctly identified
with
the Set tStudentCharges line. However, tLookup only evaluates to Error
2042,
even though the tProgCode can be found in the first column of the table.
Any
ideas on why this isn't working?
Thanks,

Deleting the Application.Names argument gave me an application-defined error
1004...
Any other ideas? Should I just change my program to a With columns(1), c =
..Find... instead of using the VLookup? Seems like it might be less
problematic.

"Nigel" wrote:

I think your lookup table assignment is not correct, try.....

Set tStudentCharges =
Workbooks("fChargesName").Worksheets("Sheet1").Ran ge("SPTable")


--
Cheers
Nigel



"LoriL" wrote in message
...
I have three workbooks - one from which I'm running by VBA code, one
workbook
with data and a third workbook with a table of charges. I want to use the
value of a program code field from the database and find that same value
in
the table of charges. For instance, one value from the database program
code
field is ASC. I want to look for that value in the first column of the
table
of charges and return that value, if it's found. Both fields are set to
text
using the format cells function. The table is sorted by ascending program
codes.

In VBA, my code is as follows:

Dim oCell as Object
Dim tProgCode as String
Dim tLookup as Variant
Dim tStudentCharges as Range

tProgCode = oCell.Offset(0,2).Value
Set tStudentCharges = Workbooks(fChargesName).Worksheets("Sheet1"). _
Range(Application.Names("SPTable"))
tLookup = Application.VLookup(tProgCode, tStudentCharges,1,False)

I have checked to see that the tProgCode is pulling the correct field. I
have also checked to make sure the table is being correctly identified
with
the Set tStudentCharges line. However, tLookup only evaluates to Error
2042,
even though the tProgCode can be found in the first column of the table.
Any
ideas on why this isn't working?
Thanks,