Lookup text in column A and change corresponding value in column B

[dss28]

In Sheet1, I have data in range A5 to A15 and corresponding data in range B5 to B15.

If I enter a text in userform1.textbox1 ( eg corresponding to say text in cell A5) and another text in textbox2, the code should search this text in textbox1 in column A of the sheet1 and replace the cell B5 text with that of textbox2 text.

can anybody help please.

 

[gordsky]

You haven't said if A5 will always be linked to B5 (or A6 to B6 ect) or if A5 could be linked with B15 for example.
Possibly not the most elegant code but assuming the rows are always the same (ie A5 = B5) you could try the following by placing in the commandbutton code of your userform.

Dim Lkfor As String, Chng As String
Dim Rng As Range, Ans As Range
Dim ws As Worksheet

Set ws = ActiveSheet 'can change this to your sheet name
Lkfor = TextBox1
Chng = TextBox2

Set Rng = ws.Range("A5:A15")     '''change to your specific range
Set Ans = Rng.Find(What:=Lkfor, LookIn:=xlValues, _
          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False)

          If Not Ans Is Nothing Then
               ws.Cells(Ans.Row, 2) = Chng
            Else
          End If

 

[dss28]

thanks a lot

You haven't said if A5 will always be linked to B5 (or A6 to B6 ect) or if A5 could be linked with B15 for example.
Possibly not the most elegant code but assuming the rows are always the same (ie A5 = B5) you could try the following by placing in the commandbutton code of your userform.