VLookup in vba for excel 2007

[DellHater]

Hi

I am having a mare!
I need to do a VLook up in VBA, been scouring the web to see how to do it and nothing works.

My latest attempt is
Sub CommentAddOrEdit()
Dim Result As String
Sheets("Sheet1").Select
' Result = Str(Application.WorksheetFunction.VLookup(D12, Range("A20:B23"), 2, False))
Result = Application.WorksheetFunction.VLookup(D12, Range("A20:B23"), 2, False)
MsgBox "The result is " & Result
End Sub

What I want to do is create a message box using the active cell as input for the VLookUp and return multiple values back and display them in a message box.

I'm crap at VBA, so apologies for any bad stuff above,
all help appreciated!

 

[SkipVought]

hi,

    dim Result as variant
    Result = Application.WorksheetFunction.VLookup([D12], Range("A20:B23"), 2, False)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DellHater]

nice one that works, how do I make the [D12] be the active cell instead ??

 

[SkipVought]

    Result = Application.WorksheetFunction.VLookup([b]ActiveCell[/b], Range("A20:B23"), 2, False)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DellHater]

ok, that works great,
problem is now that my intended solution is not what they want.
They want to insert the results of the look up in the 1st sheet.
So to recap.
a value (on sheet 1) has addition data items linked to it on sheet 2.
The values from the sheet 2 need to be found and then insterted on the sheet 1.
There can be mutliple values returned.

 

[SkipVought]

There can be mutliple values returned.

So HOW do they want multiple values returned? 1) in one cell; 2) in multiple cells?

Will there always be ONE lookup value, or could there be multiple lookup values?

I'd lean toward using a Query to return multiple rows and then processing the resultset accordingly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]