Vlookup in VB

[Fred]

I have a worksheet (Service Charges) that is being used to calculate
charges for services.

Column B is validated to contain either a team name or a free-form
entry of the users choice, from a named table on another worksheet.

Column G contains the rate for the team (from column 6 of the named
table) or a value specified by the user.

Column H contains the # of units required.

My named table (Resource_Rate_Table) is on the "Lookups" worksheet,
rows 1-160, columns A-F and has a blank entry (for the users free-form
entry) followed by 159 team name entries.

I am trying to code a Worksheet_SelectionChange routine for Service
Charges worksheet that will perform a vlookup in the table and, if the
entry in column B is found (i.e. it is a team name), it will populate
the cell in row G with the rate and then protect the cell to prevent
the user from changing it. If the entry is not found (i.e. it is not a
known team name) the cell in column G will be left unprotected for the
user to enter the rate (e.g. for Contractors).

an excerpt from my coding, so far, is as follows :

field = ActiveSheet.Cells(Start_Scan, 2).Value
' to check that I was picking up the team name
Res = Application.VLookup(field, Resource_Rate_Table, 6,
False)
If Not IsError(Res) Then
ActiveSheet.Cells(Start_Scan, 13).Value = Res
ActiveSheet.Cells(Start_Scan, 13).Locked = True
End If

However when tracing to find out why it was not working I noticed that
it said that Resource_Rate_Table=Empty and Res = Error 2042

Thanks in advance for any corrections/suggestions

Regards
Fred

 

[Jim Rech]

I haven't run your code but if Resource_Rate_Table as a defined name
referring to a range you have to tell VB that:

Application.VLookup(field, Range("Resource_Rate_Table"), 6,False)

This assumes that the defined name is global and is in the active workbook.
If it weren't you'd have to be even more specific:

Worksbooks("Workbook.xls").Worksheets("Lookups").Range("Resource_Rate_Table")

 

[Joel]

To make Jims code easier to code you can use a set statement

set rate_table = Worksbooks("Workbook.xls"). _
Worksheets("Lookups").Range("Resource_Rate_Table")
Application.VLookup(field, rate_table, 6,False)

 

[Fred]

Jim/Joel,

Many thanks, it's so obvious when you know.

Regards
Fred