Userform textbox value to row where a search string was found

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
98
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.

Rich (BB code):
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

Board Regular
Joined
Sep 12, 2013
Messages
98
I've no idea :unsure:

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

This is my search code:

VBA 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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
In that case I suspect that the Find found data in col G not col F
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
98
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...:ROFLMAO::ROFLMAO:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
If you change this
VBA Code:
Set Tbl = Sheet1.Range("F3").CurrentRegion
to this
VBA Code:
Set Tbl = Sheet1.Range("F3", Sheet1.Range("F" & Rows.Count).End(xlUp))
you wont get that problem
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top