Vlookup a text and MsgBox it thanks to VBA

[nhiribarne]

Hello everybody,

I'trying to Vlookup a value thanks to an input box and after, msg box the result, how can I do that thanks to VBA ?

I already wrote this code but it's not working.


Sub Vlookup()
' Vlookup Macro
Dim LookUpValue As String
Dim Lookup As Variant
LookUpValue = InputBox("Enter the name")

Lookup = Application.WorksheetFunction.Vlookup(LookUpValue, Sheet2.Range("B2:C5"), 2, False)
MsgBox Lookup
'MsgBox "The number is " & Lookup
End Sub


Here is my table:

Thanks by advance



A
B
C


1

ROM
1


2

TOM
2


3

DAN
3


4

NIC
4

[mperrah]

Try this,
I tested this with your data.
It asks the user for the name to find,
then messages the number it finds.


Sub matchOffset()
Dim x As Long
Dim lr, lookRng As Range
Dim findStr As String
Dim foundCell As Variant


lr = Cells(Rows.Count, "B").End(xlUp).Row
Set lookRng = Range("B1:B" & lr)

findStr = InputBox("Enter the name to find")

For x = 1 To lr
If Range("B" & x).Value = findStr Then
Set foundCell = Range("B" & x).Offset(0, 1)
MsgBox foundCell & " was found to Match"
End If
Next x

End Sub

-mark

[nhiribarne]

Ok, the code works perfectly and I understood it ^^,

But could I have done it with Application.Vlookup ?

Thank you

[mancubus]

@nhiribarne

explaining your requirement in full detail will help helpers understand your case better than insisting on some methods.

if you explain your ultimate purpose, helpers may provide different solutions, which will help you improve your vba skills.

[nhiribarne]

Ok, I'll try to be more accurate on my explanation.
I would like to learn how to use the excel function on VBA. I found this website and try to work with it (cf my first post and the macro I tried to wrote)
http://www.exceltrick.com/formulas_macros/vlookup-in-vba/

But I could not get it, telling me that there is a "bug" in the


Lookup = Application.WorksheetFunction.Vlookup(LookUpValue, Sheet2.Range("B2:C5"), 2, False) part.

If I'm not accurate enough, please, let me know :-)

Thank you for your help again

[mancubus]

when i copied the your table in a blank sheet and run the code you pasted, it worked for me.

check the range references. is your table in Sheet2 and in B2:C5 range...
check if the cells in data table have non printing characters...

[nhiribarne]

Ok, it works for me too ...
I don't know what was the problem with my workbook yesterday, thank you very much both of you guys anyway :-)

Subject solved.

[mancubus]

you are welcome.

pls mark the thread as solved from the Thread Tools dropdown which is above the first message.

[mperrah]

Check this out Using drop down Val instead of input box (less typos)

I used a dynamic named range for column B
name is LookUpB

=OFFSET(Sheet2!$B$1,0,0,COUNTA(Sheet2!$B:$B))

then added a list data validation drop down in G1 (could go anywhere)
source =LookUpB (as we just named the range of names)
13131

Drop this on the Sheet2 code. it will run the lookup when ever you change the drop down value

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 _ ' is G1, just update if you move the drop down
And Target.Row = 1 Then
Call Vlookup
End If

End Sub



then alter the Vlookup macro to this

Sub Vlookup()
' Vlookup Macro
Dim LookUpValue As String
Dim Lookup As Variant
Dim LookUpWhat As String

LookUpWhat = Sheets(2).Range("G1") ' make this where ever you want the drop down to pick the name to look up

Lookup = Application.WorksheetFunction.Vlookup(LookUpWhat, Sheet2.Range("B2:C5"), 2, False)
MsgBox Lookup
'MsgBox "The number is " & Lookup
End Sub

The cool thing with this is as you add names and numbers on sheet2, the code won't need to be changed :)

[nhiribarne]

Wow love it, thx a lot ;-)