Posts by pooky

Hi, again!
I have managed to put together excel userform for looking up data in excel!
I have created a listbox (two of them) which are filled up in two columns when the criteria meets!
But when I`m testing this thing the error mismatch pops up ( Could not set the List property. Type mismatch. Run-time error `-2147352571 (80020005)` , what does my code is missing??!?!

Here is the code:

Private Sub CommandButton4_Click()
Set xSht = Sheets("kilas")
        Lastrow = xSht.Range("A" & Rows.count).End(xlUp).Row
        strSearch = krednr.Text
                    Set aCell = xSht.Range("A2:A" & Lastrow).Find(What:=strSearch, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
row_number = 0
ListBox1.Clear
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("kilas").Range("A" & row_number)
If item_in_review = krednr.Text Then
TextBox10.Text = Sheets("kilas").Range("AE" & row_number)
With Me.ListBox1
    Me.ListBox1.ColumnCount = 2
    Me.ListBox1.ColumnWidths = "200;2000"
    .AddItem
    .List(i, 0) = Sheets("kilas").Range("V" & row_number)
    .List(i, 1) = Sheets("kilas").Range("X" & row_number)
    i = i + 1
End With
End If
Loop Until item_in_review = ""
End Sub

&

Private Sub CommandButton5_Click()
Set xSht = Sheets("nauda")
        Lastrow = xSht.Range("B" & Rows.count).End(xlUp).Row
        strSearch = krednr.Text
                    Set aCell = xSht.Range("B2:B" & Lastrow).Find(What:=strSearch, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
row_number = 0
ListBox2.Clear
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("nauda").Range("B" & row_number)
If item_in_review = krednr.Text Then
With Me.ListBox2
    Me.ListBox2.ColumnCount = 2
    Me.ListBox2.ColumnWidths = "60;60"
    .AddItem
    .List(i, 0) = Sheets("nauda").Range("A" & row_number)
    .List(i, 1) = Sheets("nauda").Range("C" & row_number)
    i = i + 1
End With
End If
Loop Until item_in_review = ""
End Sub

MODERATOR NOTE: please remember to use code tags in future as per forum rules.

This is my first thread here.... so please if you can, don`t be harsh...
There is the thing, I am trying to build a UserForm just for "simple" search.
At the moment I have two worksheets - "Dati"; "kilas"
In both ws the lookup value is in col A.

What I did already,
User will put a Client ID number in search textbox called: "krednr"
Based on this criteria it retrives data to all other textboxes form different colums
It goes for ws "Dati" and "kilas"

This is what I have:

Private Sub CommandButton1_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("Dati").Range("A" & row_number)
If item_in_review = krednr.Text Then
TextBox1.Text = Sheets("Dati").Range("G" & row_number)
TextBox3.Text = Sheets("Dati").Range("H" & row_number)
TextBox4.Text = Sheets("Dati").Range("J" & row_number)
TextBox5.Text = Sheets("Dati").Range("F" & row_number)
TextBox6.Text = Sheets("Dati").Range("R" & row_number)
TextBox7.Text = Sheets("Dati").Range("M" & row_number)
TextBox8.Text = Sheets("Dati").Range("N" & row_number)
TextBox9.Text = Sheets("Dati").Range("AI" & row_number)
End If
Loop Until item_in_review = ""
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("kilas").Range("A" & row_number)
If item_in_review = krednr.Text Then
TextBox10.Text = Sheets("kilas").Range("D" & row_number)
End If
Loop Until item_in_review = ""

End Sub

But today I spent 4hours trying to find solution for cases when there is a multiple records for one client ID
I need them to show in one text box or listbox....

Example:

[TABLE="class: grid, width: 500"]

A

B

C

Client ID

Pet

Count

123

cat

1

177

dog

1

185

lion

1

123

dog

2

123

puma

1

[/TABLE]

Like for examp.:
If i insert Client ID "123"
It will show result
cat 1
dog 2
puma 1

And all in one "BOX"!

Where should i start?!