Excel VBA userform vlookup + listbox question

[kimdogul]

Before talking about question, i want to tell you that i am from South korea and there isn't enough
information in southkorea about excel VBA. so i came here.

i have studied literacture subjects so i maybe not good at understanding the "method" of vba.
i just try, and fix, and try, and fix what other people wrote in here.


what i am going to do is,

when i type "Apple corp" in textbox1,
and click the searchcommandbutton,

they search from sheet2 with vlookup or something else...

and show every apple corp's record on list box

sheet2 is like this

NAME OF CORP HOW MUCH DID THEY SELL
apple corp 3
apple corp 4
apple corp 18
apple corp 4
banana corp 5
pineapple corp 13

i am using userform.

i really need your help sir.

 

[kimdogul]

i use like this


Private Sub cmbCorpSearch_Click()


Dim 업체 As Worksheet: Set 업체 = Sheets("sheet2")
Dim 범위2 As Range: Set 범위2 = 업체.Range("a2:z100")
Dim 찾을값2 As String
Dim c As Variant
Dim d As Variant
Dim e As Variant




찾을값2 = "*" & txt업체명 & "*"


c = Application.VLookup(찾을값2, 범위2, 11, True)
d = Application.VLookup(찾을값2, 범위2, 7, True)
e = Application.VLookup(찾을값2, 범위2, 8, True)


If IsError(c) Then
MsgBox "찾으시는 값이 없습니다, 업체명을 확인해주세요", vbOKOnly

Else


ListBox1.ColumnCount = 3
ListBox1.AddItem c
ListBox1.List(0, 1) = d
ListBox1.List(0, 2) = e
End If




End Sub

 

[AlphaFrog]

Try something like this.

It uses the Find method

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmbCorpSearch_Click()
    
    [COLOR=darkblue]Dim[/COLOR] Found [COLOR=darkblue]As[/COLOR] Range, FirstFound [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] TextBox1.Value = "" [COLOR=darkblue]Then[/COLOR] MsgBox "Missing search term.", vbExclamation, "": [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    ListBox1.Clear
    ListBox1.ColumnCount = 3
    ListBox1.ColumnWidths = "50;25;25"
    
    [COLOR=darkblue]With[/COLOR] Sheets("Sheet2").Range("A:A")
        
        [COLOR=darkblue]Set[/COLOR] Found = .Find(What:=TextBox1.Value, _
                          LookIn:=xlValues, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False)
                          
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            FirstFound = Found.Address
            [COLOR=darkblue]Do[/COLOR]
                [COLOR=darkblue]With[/COLOR] ListBox1
                    .AddItem Found.Value
                    .List(.ListCount - 1, 1) = Found.Offset(, 1).Value
                    .List(.ListCount - 1, 2) = Found.Offset(, 2).Value
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                
                [COLOR=darkblue]Set[/COLOR] Found = .FindNext(After:=Found)
            
            [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] Found.Address = FirstFound
            
        [COLOR=darkblue]Else[/COLOR]
            MsgBox TextBox1.Text, vbExclamation, "No Match Found"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]