VBA - vlookup

[MoonWeazel]

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

 

[mangesh_yadav]

Application.WorksheetFunction.VLookup()

- Manges

 

[MoonWeazel]

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

 

[Tom Ogilvy]

Depending on where the activecell is and what value you want to lookup, some
possibilities would be:

ActiveCell.Characters.Text =
Application.WorksheetFunction.VLookup(ActiveCell.Value, lookup_range, 2,
False)

or

ActiveCell.Characters.Text =
Application.WorksheetFunction.VLookup(ref(i), lookup_range, 2, False)

if ref is a single column Range

 

[MoonWeazel]

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

 

[Tom Ogilvy]

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]

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!!!!