Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VB?
Not very robust, but:
Sub FindCell() Dim myval As Long Set myrng = Sheets("Inventory").Range("C3:C6") myval = Application.WorksheetFunction. _ Match("181006", myrng, 0) + 2 MsgBox "Cell: " & Cells(myval, 7). _ Address(RowAbsolute:=False, ColumnAbsolute:=False) End Sub --- The macro uses MATCH to find the row index of "181006" in the range C3:C6, and then adds 2 to give the correct row. Because your 3rd argument was 5 in your VLOOKUP, that translates to column G, which is why you see "7" in the Cells(myval.7) HTH Jason Atlanta, GA -----Original Message----- I'm writing some code for a simple VB tool, and have come accross a problem. I basically need my code to do a vlookup for a number, then return the cell reference for the cell in question. eg: Find the cell in a given range range containing the number 181006, then tell me the ref of the cell that is the fifth collumn along. The vlookup would be: =VLOOKUP("181006",Inventory!C3:H6,5,FALSE) If anyone knows, or can point me somewhere i might find the answer i would really appreciate it, and you never know, my boss might even let me leave before it gets dark tonight. :-) . |
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 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 | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |