Solved: Use Userform instead

[ndendrinos]

Hello,
I've "borrowed" MdMackillop's code and changed it a bit .
Now if I type in cell B1 I get the data from Sheet2 the way I need it.
I would prefer to use the userform but cannot change the code to replace
the value of B1 with the value of the textbox in the form and make it work.

Here is the file and thank you for any help you might offer
Nick

[Erdin? E. Ka]

First, deactivate or delete Sheet2's Worksheet_Change event codes. Then, write the codes below to CommandButton1's Click event.
I think that you want to do like this.


Private Sub CommandButton1_Click()

Dim Cell As Range
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim LoopCounter As Byte

Set Sheet1 = Sheets("Sheet1")
Set Sheet2 = Sheets("Sheet2")

LastUsedCell = WorksheetFunction.CountA(Sheet2.Range("B:B"))
LoopCounter = 0

For Each Cell In Sheet2.Range("B1:B" & LastUsedCell)
If Cell.Value = Val(Me.TextBox1) Then
LoopCounter = 1
Sheet1.Activate
[B2] = Cell.Value
[C2] = Cell.Offset(0, 1).Value
[D2] = Cell.Offset(0, 2).Value
[E2] = Cell.Offset(0, 3).Value
[F2] = Cell.Offset(0, 4).Value
[G2] = Cell.Offset(0, 5).Value
[H2] = Cell.Offset(0, 6).Value
[I2] = Cell.Offset(0, 7).Value
[J2] = Cell.Offset(0, 8).Value
[K2] = Cell.Offset(0, 9).Value
[L2] = Cell.Offset(0, 10).Value
End If
Next Cell

If LoopCounter <> 1 Then
MsgBox "Not Found!", vbCritical
Me.TextBox1 = ""
Me.TextBox1.SetFocus
End If

End Sub

[ndendrinos]

Thank you Erdin? E. Kara?am your code works but I need to adjust it to search by name and return the whole row from sheet2 including the name
Regards,
Nick Dendrinos

[Erdin? E. Ka]

Hi Nick Dendrinos, :hi:


Change the macro as;

Private Sub CommandButton1_Click()

Dim Cell As Range
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim LoopCounter As Byte

Set Sheet1 = Sheets("Sheet1")
Set Sheet2 = Sheets("Sheet2")

LastUsedCell = WorksheetFunction.CountA(Sheet2.Range("B:B"))
LoopCounter = 0

For Each Cell In Sheet2.Range("A1:A" & LastUsedCell)
If UCase(Cell.Value) = UCase(Me.TextBox1) Then
LoopCounter = 1
Sheet1.Activate
[A2] = UCase(Cell.Value)
[B2] = Cell.Offset(0, 1).Value
[C2] = Cell.Offset(0, 2).Value
[D2] = Cell.Offset(0, 3).Value
[E2] = Cell.Offset(0, 4).Value
[F2] = Cell.Offset(0, 5).Value
[G2] = Cell.Offset(0, 6).Value
[H2] = Cell.Offset(0, 7).Value
[I2] = Cell.Offset(0, 8).Value
[J2] = Cell.Offset(0, 9).Value
[K2] = Cell.Offset(0, 10).Value
[L2] = Cell.Offset(0, 11).Value
End If
Next Cell

If LoopCounter <> 1 Then
MsgBox "Not Found!", vbCritical
Me.TextBox1 = ""
Me.TextBox1.SetFocus
End If

End Sub


I think that now it's ok.:friends:

[ndendrinos]

Than you again Erdin? E. Kara?am it is just perfect now
Regards
:friends:
Nick

[Erdin? E. Ka]

Not At All! Nick,:hi:
I am happy to help you, :*)
Yours truly...

[mdmackillop]

Hi Erdin?
While looping works, it's not the most efficient way to get the match in this case, especially if there are thousands of entries. Use Option Compare Text to avoid the capitalisation problem, and Find to locate the data. You can then copy the row data to the result area.

Option Explicit
Option Compare Text

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub CommandButton1_Click()
Dim Cel As Range
Set Cel = Sheets("Sheet2").Columns(1).Find(TextBox1)
Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")
End Sub

[mdmackillop]

You could also use a combobox for your data entry; There's a KB item for filling the combo with an ordered list if necessary http://vbaexpress.com/kb/getarticle.php?kb_id=824

Option Explicit
Option Compare Text

Private Sub UserForm_Initialize()
With Sheets("Sheet2")
ComboBox1.List = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value
End With
End Sub

Private Sub ComboBox1_Change()
Dim Cel As Range
Set Cel = Sheets("Sheet2").Columns(1).Find(ComboBox1)
Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

[Erdin? E. Ka]

Hi my dear teacher mdmackillop :yes

Thank you for your warning and help, from now on i will use your method.:bow:
It's faster and short than mine.

Best Regards.

[ndendrinos]

My sincere thanks to you too mdmackillop .