Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Good Morning All,
I have a spreadsheet with the following columns used to trac dependencies within projects: A - ID field (number) B - Name of ID (text) thru' to W - dependency (number - one of the IDs from col A) I am trying to write some code that will lookup the dependency i column a and return the value in column b The vlookup formula would be in X1 and would look like this: =vlookup(W1, A1:B10,2,false) - (this does work!) How would I turn this into a nice piece of VBA? Any help would be greatfully received!! TIA, MoonWeaze -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Thanks for that!!
So far I have this: Dim i As Integer Dim lookup_range As Range Dim ref As Range Set ref = Sheets("Product description").Range("ref") Set lookup_range = Sheets("Produc description").Range("lookup_range") For i = 1 To ref.Rows.Count ActiveCell.Characters.Text Application.WorksheetFunction.VLookup(i, lookup_range, 2, False) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Next End Sub This does work, but it uses the count value of the cell rather than th value of the cell. So if the first cell has the value of 15, the looku formula uses 1 instead of 15. I don't know how to change this and the XL help is not being ver helpful. TIA, MoonWeaze -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Depending on where the activecell is and what value you want to lookup, some
possibilities would be: ActiveCell.Characters.Text = Application.WorksheetFunction.VLookup(ActiveCell.V alue, lookup_range, 2, False) or ActiveCell.Characters.Text = Application.WorksheetFunction.VLookup(ref(i), lookup_range, 2, False) if ref is a single column Range -- Regards, Tom Ogilvy "MoonWeazel " wrote in message ... Thanks for that!! So far I have this: Dim i As Integer Dim lookup_range As Range Dim ref As Range Set ref = Sheets("Product description").Range("ref") Set lookup_range = Sheets("Product description").Range("lookup_range") For i = 1 To ref.Rows.Count ActiveCell.Characters.Text = Application.WorksheetFunction.VLookup(i, lookup_range, 2, False) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Next End Sub This does work, but it uses the count value of the cell rather than the value of the cell. So if the first cell has the value of 15, the lookup formula uses 1 instead of 15. I don't know how to change this and the XL help is not being very helpful. TIA, MoonWeazel --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Hi Tom,
Thanks!! That works like an absolute treat! You star!!! Is there anyway to skip blank cells? Some of the items don't have dependency and the VB falls over. Any help would be really really really appreciated!!! TIA, MoonWeaze -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Dim i As Integer
Dim lookup_range As Range Dim ref As Range Set ref = Sheets("Product description").Range("ref") Set lookup_range = Sheets("Product description").Range("lookup_range") For i = 1 To ref.Rows.Count if ref(i).Value < "" Then ' lookup code you are using End if ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Next "MoonWeazel " wrote in message ... Hi Tom, Thanks!! That works like an absolute treat! You star!!! Is there anyway to skip blank cells? Some of the items don't have a dependency and the VB falls over. Any help would be really really really appreciated!!! TIA, MoonWeazel --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - vlookup
Dearest Tom,
You are wonderful!! Thankyou soooooo much! I really appreciate this! You have stopped me getting desk induced concussion! Fantastic!! A very happy MoonWeazel!!!! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |