How do i convert this code using VLookUp function?

Question

I am writing a vba code to retrieve a value from a different sheet. so when i press CommandButton2, an InputBox will apppear and user need to input a set of member number, after that the code will the name of the member based on the member number. i wrote it like this code below. Right now, i'm trying to rewrite the code with vlookup function. But i cant seem to make it work, can someone help me to fix it? (I'm a newbie so if there are mistakes, im truly sorry :)) (fyi: the commandbutton2 is in sheet1(inq) , and the memberlist in sheet4(member) Range(A4:B12)

Private Sub CommandButton2_Click()
    Dim memberlists As Range
    Dim memberfinder As String
    Dim searchCell As Range
    Dim searchValue As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("member")
    Set memberlists = ws.Range("A4:B12")
    memberfinder = InputBox("Input your member number", "MemberFinder", "Example:100123")
    
    On Error Resume Next
    Set searchCell = memberlists.Columns(1).Find(memberfinder, LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    
    If searchCell Is Nothing Then
        MsgBox "No member found. Please register first"
    Else
        searchValue = searchCell.Offset(0, 1).Value
        MsgBox "Member ID: " & memberfinder & vbNewLine & "Member Name: " & searchValue
    End If
End Sub

vlookup:

Private Sub CommandButton2_Click()
    Dim memberlist As Range
    Dim memberfinder As String
    Dim searchValue As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("member")
    Set memberlist = ws.Range("A2:B12")
    memberfinder = InputBox("Input your member number", "MemberFinder", "Example:100123")
    
    searchValue = Application.VLookup(memberfinder, memberlist, 2, False)
    
    If IsError(searchValue) Then
        MsgBox "No member found. Please register first"
    Else
        MsgBox "Member ID: " & memberfinder & vbNewLine & "Member Name: " & searchValue
    End If
End Sub

this is the memberlist

Try `Application.VLookup(CLng(memberfinder), memberlist, 2, False)` — CDP1802, Jun 09 '23 at 17:47
i've solved it bro, memberfinder needs to stay as tring, because if i define it as integer, the value that i put into the input box will be too big for integer to contain. Thank u guys. — Ryan Mulya, Jun 10 '23 at 07:32

Black cat · Answer 1 · 2023-06-10T14:23:00.800

0

Change the memberfinder declaration like this:

From:

    Dim memberfinder As String

To:

    Dim memberfinder As Integer ' range -32,768 to 32,767

Or

    Dim memberfinder As Long    ' range -2,147,483,648 to 2,147,483,647

edited Jun 10 '23 at 14:23

answered Jun 10 '23 at 05:30

Black cat

1,056
1
2
11

i've tried it and debugger says overflow – Ryan Mulya Jun 10 '23 at 07:28
i've solved it bro, memberfinder needs to stay as tring, because if i define it as integer, the value that i put into the input box will be too big for integer to contain. – Ryan Mulya Jun 10 '23 at 07:32

score 0 · Answer 2 · answered Jun 10 '23 at 07:33

i've solved it bro, memberfinder needs to stay as string, because if i define it as integer, the value that i put into the input box will be too big for integer to contain. And we will need to convert the string to long with this function Application.VLookup(CLng(memberfinder), memberlist, 2, False). And everything is working fine. Thank you guys so much.

How do i convert this code using VLookUp function?

2 Answers2