Userform textbox value to row where a search string was found

[Jimmypop]

Good day

Some help required again…

I have a userform where I input a number and search for that number. Once found the info on the userform textboxes will be populated with values from cells on the worksheet.

E.G. I search for 5555 (numbers vary) and info is displayed on userform in textboxes.

TextBox1.Value = B4
TextBox2.Value = C4
TextBox3.Value = D4
TextBox4.Value = E4
TextBox5.Value = F4
TextBox6.Value = G4
TextBox7.Value = H4
TextBox8.Value = I4
'Note that info will not come from row 4 all the time. In this instance my search string of 5555 appears in row 4.

Textboxes are numbered Textbox1 to Textbox88 and info starts from Column B Row 3 and runs to Column CK.

Once this info is displayed I want to be able to change the values in the specific row where info was pulled from with the current textbox values… so let’s say I want to update the 5555 to 5566 then the entire row should be updated also. Info is updated with a command button.

 

[Jimmypop]

I've no idea

Which code are you talking about & where did you put the values?

This is my search code:

Private Sub SrchEmp_Click()
    Dim tbl As Range ' this will be the table to search
    Set tbl = Sheet1.Range("F3").CurrentRegion
     'run the search
    Set fnd = tbl.Find(What:=TextBox5.value, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
     'if the item is not found the user is informed and the textbox cleared
    If fnd Is Nothing Then
        MsgBox "Employee not found on Database!", 16, "Message from Employee Update"
        TextBox5.value = ""
        Exit Sub
        'if the item is found the details will be copied to the labels
    Else: fnd.Activate
    End If

   Dim i As Long
   For i = 1 To 88
      Me.Controls("TextBox" & i).value = fnd.Offset(, i - 5).value
   Next i
   
End Sub

So it pulls info from the row where the search item is inserted into textbox5 and SrchEmp is clicked. It finds the values but does not put them in the correct text boxes on userform...

So info from Col B should be in Textbox1, Col C - TextBox2, Col D - TextBox3 etc until eventually ColCK should be in TextBox88 but when I do the search it starts with ColC in Textbox1 and Textbox88 which should have value from ColCK, is left blank

 

[Fluff]

In that case I suspect that the Find found data in col G not col F

 

[Jimmypop]

In that case I suspect that the Find found data in col G not col F

I almost said what Mrs. Brown would say ? ? you know what I was doing... as you said... i was searching with the wrong value...works now as previous... Was supposed to use value from ColF but was using Info from ColG the whole time... I need a stiff drink and need to go home...

 

[Fluff]

If you change this

Set Tbl = Sheet1.Range("F3").CurrentRegion

to this

Set Tbl = Sheet1.Range("F3", Sheet1.Range("F" & Rows.Count).End(xlUp))

you wont get that problem