Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VB
Jim/Joel,
Many thanks, it's so obvious when you know. Regards Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |