Vlookup in VB
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 |
Vlookup in VB
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").R ange("Resource_Rate_Table") -- Jim "Fred" wrote in message oups.com... 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 |
Vlookup in VB
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) "Jim Rech" wrote: 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").R ange("Resource_Rate_Table") -- Jim "Fred" wrote in message oups.com... 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 |
Vlookup in VB
Jim/Joel,
Many thanks, it's so obvious when you know. Regards Fred |
All times are GMT +1. The time now is 09:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com