Excel VBA - Find & Replace using Userform

[Rokine]

Please I have a Userform with combobox1, combobox2 and Textbox1, and the associated Sheet is "sample". Tabulated data in the Sheet is shown below:
A B C R
1 AB001 T21 25
2 AB001 T22 25
3 AB003 T23 20
4 AB003 T24 20
5 AB003 T25 20
6 AB002 T26 15
7 AB001 T27 25
8 AB003 T28 20

At a click of a button, I want to compare combobox1 value (string) with values in column B, and a match is found, those values (in column B) should be replaced with combobox2 value and the corresponding rows in column R should be replaced with Textbox1 value. For example, given that combobox1.Value = "AB003", combobox2.Value = "AB005" and Textbox1.Value = 32, then
1. Replace Rows 3, 4, 5 & 8 of column B with "AB005"
2. Replace Rows 3, 4, 5 & 8 of column R with 32

Please your help will be greatly appreciated.

I have tried the code below and it is not working for me:
Dim Lastrow As Long, ws As Worksheet
Set ws = Worksheets("sample")
Lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
If Not IsError(Application.Match(Trim(Me.Combobox1.Value), ws.Range("B:B"), 0)) Then
ws.Cells(i, "B").Value = Me.Combobox2.Value.Value
ws.Cells(i, "R").Value = Me.Textbox1.Value
End If
Next i

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
  Dim i As Long
  Dim ws As Worksheet
  
  Set ws = Sheets("sample")
  For i = 2 To ws.Range("B" & Rows.Count).End(3).Row
    If ws.Range("B" & i).Value = ComboBox1.Value Then
      ws.Range("B" & i).Value = ComboBox2.Value
      ws.Range("R" & i).Value = TextBox1.Value
    End If
  Next
End Sub

----------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Dante Amor
	A	B	C															R
1	Header1	Header2	Header3															Header18
2	1	AB001	T21															25
3	2	AB001	T22															25
4	3	AB005	T23															32
5	4	AB005	T24															32
6	5	AB005	T25															32
7	6	AB002	T26															15
8	7	AB001	T27															25
9	8	AB005	T28															32
sample

------------------
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[Rokine]

Try this:

Private Sub CommandButton1_Click()
  Dim i As Long
  Dim ws As Worksheet
 
  Set ws = Sheets("sample")
  For i = 2 To ws.Range("B" & Rows.Count).End(3).Row
    If ws.Range("B" & i).Value = ComboBox1.Value Then
      ws.Range("B" & i).Value = ComboBox2.Value
      ws.Range("R" & i).Value = TextBox1.Value
    End If
  Next
End Sub

----------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Dante Amor
	A	B	C															R
1	Header1	Header2	Header3															Header18
2	1	AB001	T21															25
3	2	AB001	T22															25
4	3	AB005	T23															32
5	4	AB005	T24															32
6	5	AB005	T25															32
7	6	AB002	T26															15
8	7	AB001	T27															25
9	8	AB005	T28															32
sample

------------------
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

DanteAmor, this is amazing and I thank you so much. Your advice is also well taken and I will do it better next time. Once again, thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Rokine]

Please DanteAmor, sorry to bother you but I will need your help again. As part of my strategy for the learning Excel VBA, I am working on a sample project and that is why I keep coming to you when I get stuck.

My request is that I have a Userform for populating the "sample" table below. The form controls txtInterval, cmbStatus, cmbBhid, txtFrom, txtTo, txtSampID add data to the fields Interval, Status, BHID, From, To, SampID respectively. User enters data in txtInterval, cmbStatus, cmbBhid, and txtFrom, txtTo, txtSampID will be automatically populted if a condition is met. I have written a code which works fine for the first 2 ifs. However, if the 3rd or 4th condition is met, the code is not able to maintain the pattern that txtFrom and txtTo follows in the table. The pattern is that if cmbBhid value doesn't exist in Bhid field of table, Me.txtFrom.Value = 0 and Me.txtTo.Value = Me.txtInterval.Value. But, if cmbBhid value exists in Bhid field of table:
1. Find the cmbBhid value in the table, get the greatest "txtFrom Value", add txtInterval value to it and assign it to Me.txtFrom.Value
2. Find the cmbBhid value in the table, get the greatest "txtTo Value", add txtInterval value to it and assign it to Me.txtTo.Value

I am able to display the txtFrom and txtTo Values on a MsgBox but unable to add it to add them to the Me.txtFrom.Value and Me.txtTo.Value. In the table, the pattern is illustrated with different background colours from RNO 1 to 10, and RNO 11 to 16 are the results expected when new data is captured. Please help me if you can.

Book1
	A	B	C	D	E	F	G
1	RNO.	Interval	Status	BHID	From	To	SampID
2	1	1.5	Y	AB1	0.0	1.5	RMT111
3	2	1.5	Y	AB1	1.5	3.0	RMT111
4	3	1.5	Y	AB2	0.0	1.5	RMT111
5	4	1.5	Y	AB2	1.5	3.0	RMT111
6	5	1.5	Y	AB2	3.0	4.5	RMT111
7	6	1.5	Y	AB1	3.0	4.5	RMT111
8	7	1.5	N	AB3	0.0	1.5	AB3_0_1.5
9	8	1.5	Y	AB2	4.5	6.0	RMT111
10	9	1.5	Y	AB3	1.5	3.0	RMT111
11	10	1.5	N	AB3	3.0	4.5	AB3_3_4.5
12	11	1.5	N	AB2	6.0	7.5	AB2_6_7.5
13	12	1.5	N	AB3	4.5	6.0	AB3_4.5_6
14	13	1.5	Y	AB3	6.0	7.5	RMT111
15	14	1.5	Y	AB1	4.5	6.0	RMT111
16	15	1.5	Y	AB5	0.0	1.5	RMT111
17	16	1.5	Y	AB5	1.5	3.0	RMT111
Sheet1

Dim ws As Worksheet, i As Long, LastRow As Long
Me.txtInterval.Value  = 1.5
Set ws = Worksheets("sample")
lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
  If ws.Range("D" & i).Value <> Me.cmbBhid.Value And Me.cmbStatus.Value = "N" Then
     Me.txtFrom.Value = 0
     Me.txtTo.Value = Me.txtInterval.Value
     Me.txtSampID.Value = Me.cmbBhid.Value & "_" & Me.txtFrom.Value & "_" & Me.txtTo.Value
  ElseIf ws.Range("D" & i).Value <> Me.cmbBhid.Value And Me.cmbStatus.Value = "Y" Then
     Me.txtFrom.Value = 0
     Me.txtTo.Value = Me.txtInterval.Value
     Me.txtSampID.Value = "RMT111"
  ElseIf ws.Range("D" & i).Value = Me.cmbBhid.Value And Me.cmbStatus.Value = "N" Then
     Me.txtFrom.Value = ws.Range("E" & i).Value + Val(Me.txtInterval.Value)
     Me.txtTo.Value = ws.Range("F" & i).Value + Val(Me.txtInterval.Value)
     Me.txtSampID.Value = Me.cmbBhid.Value & "_" & Me.txtFrom.Value & "_" & Me.txtTo.Value
  ElseIf ws.Range("D" & i).Value = Me.cmbBhid.Value And Me.cmbStatus.Value = "Y" Then
     Me.txtFrom.Value = ws.Range("E" & i).Value + Val(Me.txtInterval.Value)
     Me.txtTo.Value = ws.Range("F" & i).Value + Val(Me.txtInterval.Value)
     Me.txtSampID.Value = "RMT111"
  End If
  Next i

 

[Siddharth Rout]

Please I have a Userform with combobox1, combobox2 and Textbox1, and the associated Sheet is "sample". Tabulated data in the Sheet is shown below:

Not trying to self promote here but this is something that I have spoken about in Editing and Deleting data from Worksheet using Userform

Instead of looping through every row, use .Find to search for the relevant value and then use .Offset to get the values in the found row. This will be much faster than looping.

 

[Rokine]

Not trying to self promote here but this is something that I have spoken about in Editing and Deleting data from Worksheet using Userform

Instead of looping through every row, use .Find to search for the relevant value and then use .Offset to get the values in the found row. This will be much faster than looping.

Thank you Siddharth for your suggestion. But please some further guidance here will be much appreciated. As shown in my table above, assuming I have populated the table up to row 11 (RNO 10) and the next BHID value to capture in row 12 is "AB2". "AB2" are found in rows 4, 5, 6 and & 9, but I want to be able to land on specifically "AB2" in row 9 (because it has greatest FROM and To values or let's say it has the highest RNO among the "AB2") so that I can offset to fetch the corresponding E and F columns values. Please can you help me on how to do this?

 

[Rokine]

Hi Siddharth, I get the logic in what you are saying. I will try it and see how it works for me. I misunderstood you initially because my objective is to be able to implement the logic above automatically without the need to select it manually from the listbox and edit it in the Userform. Thanks so much, however, if there is a way to automate it, it is gladly welcome.

 

[Siddharth Rout]

Thank you Siddharth for your suggestion. But please some further guidance here will be much appreciated. As shown in my table above, assuming I have populated the table up to row 11 (RNO 10) and the next BHID value to capture in row 12 is "AB2". "AB2" are found in rows 4, 5, 6 and & 9, but I want to be able to land on specifically "AB2" in row 9 (because it has greatest FROM and To values or let's say it has the highest RNO among the "AB2") so that I can offset to fetch the corresponding E and F columns values. Please can you help me on how to do this?

Is this what you are trying? I used a basic code with hardcoded search string. Feel free to edit it to suit your need.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim SearchString As String
    Dim Rno As Long
   
    '~~> This is the search string
    SearchString = "AB2"
   
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
   
    With ws
        '~~> Search for search string in col D
        Set aCell = .Columns(4).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
       
        '~~> Check if found
        If Not aCell Is Nothing Then
            Set bCell = aCell
            
            '~~> Check if value in column 1 > RNO. If yes, then store it
            If .Range("A" & aCell.Row).Value2 > Rno Then Rno = .Range("A" & aCell.Row).Value2
            
            '~~> Use the .FindNext in a loop to find the rest of the search strings
            Do
                Set aCell = .Columns(4).FindNext(After:=aCell)
    
                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    If .Range("A" & aCell.Row).Value2 > Rno Then Rno = .Range("A" & aCell.Row).Value2
                Else
                    Exit Do
                End If
            Loop
        End If
       
        '~~> Display the result of E and F
        If Rno <> 0 Then
            MsgBox "The data that you are looking for is " & vbNewLine & _
            "Row Number : " & Rno + 1 & vbNewLine & _
            "From : " & .Range("E" & Rno + 1).Value2 & vbNewLine & _
            "To : " & .Range("F" & Rno + 1).Value2
        End If
    End With
End Sub

Screenshot

 

[Rokine]

Is this what you are trying? I used a basic code with hardcoded search string. Feel free to edit it to suit your need.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim SearchString As String
    Dim Rno As Long
 
    '~~> This is the search string
    SearchString = "AB2"
 
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
 
    With ws
        '~~> Search for search string in col D
        Set aCell = .Columns(4).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
     
        '~~> Check if found
        If Not aCell Is Nothing Then
            Set bCell = aCell
          
            '~~> Check if value in column 1 > RNO. If yes, then store it
            If .Range("A" & aCell.Row).Value2 > Rno Then Rno = .Range("A" & aCell.Row).Value2
          
            '~~> Use the .FindNext in a loop to find the rest of the search strings
            Do
                Set aCell = .Columns(4).FindNext(After:=aCell)
  
                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    If .Range("A" & aCell.Row).Value2 > Rno Then Rno = .Range("A" & aCell.Row).Value2
                Else
                    Exit Do
                End If
            Loop
        End If
     
        '~~> Display the result of E and F
        If Rno <> 0 Then
            MsgBox "The data that you are looking for is " & vbNewLine & _
            "Row Number : " & Rno + 1 & vbNewLine & _
            "From : " & .Range("E" & Rno + 1).Value2 & vbNewLine & _
            "To : " & .Range("F" & Rno + 1).Value2
        End If
    End With
End Sub

Screenshot

View attachment 70529

Hi Sid, the code worked perfectly. Thank you very much for this great kindness.