userform textbox that searches column b

I have spent hours scrolling up and down this sheet looking for part numbers, so I created a userform with 2 textboxes. I want to enter a part number in the first textbox, and have the macro select the cell in column B that contains that part#. Then I will tab to the second textbox and enter a quantity. When I tab back to the first textbox, the quantity should paste into the next empty cell in the same row of the part# from textbox 1.

Textbox1 = 4110  Textbox2 = 500

	B	C	D	E
1	1010	25		
2	1020	14	15	
3	1030	19	11	
4	2020	100	10	
5	2030	23		
6	4100	67		
7	4110	24		
8	4150	24		
9	5010	10

On first tab, cell B7 would be selected, on second tab, 500 would be placed in cell D7.

I tried converting code from a similar post, but I ended up with extra code that crashes excel every time I try to save the file.

Any help is appreciated,

Thank you


(I already know how to create a userform and set up tab orders)

Hi,

Try something like..

Private Sub CommandButton1_Click()
Dim Rng As Range, v1, v2, r As Range
Set Rng = Range("b1:d" & Range("b" & Rows.Count).End(xlUp).Row)
If Len(Me.TextBox1) > 0 Then v1 = Me.TextBox1.Value
If Len(Me.TextBox2) > 0 Then v2 = Me.TextBox2.Value
On Error Resume Next
With Rng
    Set r = .Columns(1).Find(what:=v1, lookat:=xlWhole).Offset(, 2)
    On Error GoTo 0
End With
If Not r Is Nothing Then
    r.Value = v2
    MsgBox "Done!"
Else
    MsgBox "Value '" & v1 & "' not found!", vbInformation
    Exit Sub
End If
End Sub

HTH

Originally Posted by Cheeky Charlie

Fixing this might be quicker than starting from scratch - perhaps you could post it (don't forget code tags).

HTH

Ok, actually the first textbox is actually a combobox (sometimes I dont have to type the whole part# because of the rowsource) "cbo1"

The original code would search a column on multiple sheets. I want it to stay on the same sheet (Adjust1!).

Private Sub Userform_Initialize()
With cbo1
    cbo1.RowSource = "Adjust1!B5:B517"
End With
    cbo1.Value = ""

End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
Dim ans As Variant

sStr = cbo1.Value

For Each sh In ActiveWorkbook.Worksheets
  Set rng = sh.Cells.Find(What:=sStr, _
   After:=sh.Range("B1"), _
   LookIn:=xlFormulas, _
   LookAt:=xlWhole, _
   SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, _
   MatchCase:=False)
 If Not rng Is Nothing Then
    firstAddress = rng.Address
    
    
        Application.Goto rng, True
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 rng.Select
    
    Do
    
    If IsEmpty(ActiveCell) = False Then
    
        ActiveCell.Offset(0, 1).Select
        
    End If
    
    Loop Until IsEmpty(ActiveCell) = True
    
    ActiveCell.Value = TextBox1.Value
        
        cbo1.Value = ""
        TextBox1.Value = ""
 Exit Sub
      End If
Next
End Sub

Originally Posted by Cheeky Charlie

That's not very nice - try this:


Although I'm not sure what was causing a crash - this might be something to do with other coding elsewhere(?)

CC

That very well could be. Its a complex (for me anyway) multiple file inventory tracking spreadsheet.

A couple people didnt show up for work today, so I probably wont have time to test this today. Im sure it will work fine, I will let you know when I can get to it.

Thank you,