Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA using VLookUp
I have two workbooks open: book2.xls and Dept_IDs.xls
I want to use VBA to determine if a value in cell A7 of Sheet1 of book2.xls needs to have its cell colored based on the 3rd column of data in a range named: Dept_ID_T in the workbook: Dept_IDs. IE, if the VLookUp([book2.xls]Sheet1!A7,[Dept_IDs_0911.xls]Dept_ID_T_WS!Dept_ID_T,3,FALSE) = "410-50", then I want to color cell A7 a light yellow. What's the VBA to determine if the vLookUp would return the value "410-50"? Your help will be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA using VLookUp
Try this. I used FIND instead of VLOOKUP. I also set variables for each
item to make it easier to understand the code and to debug the code. Set bk = thisworkbook Set sht = bk.sheets("Sheet1") Set Deptbk = workbooks("Dept_IDs_0911.xls") set DeptSht = Deptbk.sheets("Dept_ID_T_WS") LookupValue = sht.range("A7") set c = DeptSht.Range(Dept_ID_T).find(what:=LookupValue, _ lookin:=xlvalues,lookat:=xlwhole) if c is nothing then msgbox("did not find : " & Lookupvalue) else 'get column 3 of table Col_3 = c.offset(0,2) if Col_3.value = "410-50" then sht.range("A7").interior.colorindex = 6 end if end if "ND Pard" wrote: I have two workbooks open: book2.xls and Dept_IDs.xls I want to use VBA to determine if a value in cell A7 of Sheet1 of book2.xls needs to have its cell colored based on the 3rd column of data in a range named: Dept_ID_T in the workbook: Dept_IDs. IE, if the VLookUp([book2.xls]Sheet1!A7,[Dept_IDs_0911.xls]Dept_ID_T_WS!Dept_ID_T,3,FALSE) = "410-50", then I want to color cell A7 a light yellow. What's the VBA to determine if the vLookUp would return the value "410-50"? Your help will be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |