Find & Search in a specific range of cells with VBA RRS feed

  • Question

  • Hi everybody,

    I am really new in VBA  and I'm trying to generate a code to find and replace strings and numbers only in column A. The current code I already use finds and replace in the whole Worksheet. Please help to modify the current code.

    Thanks :)

    Sub SearchAndReplace()
    Dim WS As Worksheet
    Dim Replace As String
    Dim search As String
    Dim P As String
    Dim T As String
    P = "Value you want to replace"
    T = "Find and Replace Value"
    FindTarget = InputBox(P, T)
    P = "New Value"
    T = "Find and Replace Value"
    ReplaceTarget = InputBox(P, T)
    For Each WS In Worksheets
    WS.Cells.Replace What:=FindTarget, Replacement:=ReplaceTarget, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End Sub

    Monday, January 29, 2018 1:53 PM

All replies

  • WS.Cells.Replace What:=Find.....

    WS.Range("A:A").Replace What:=Find....

    Monday, January 29, 2018 5:24 PM
  • Thank you very much!!
    Tuesday, January 30, 2018 6:12 AM
  • I have one more question

    I have a User form, and I want each time it opens to display at the first Text box (called ID) the last value of column A +1.

    I have this code but each time I open the user form appears nothing and does not increment the number sequence according to the last number of column A.

    Thanks in advance!!

    Private Sub UserForm_Click()
    Dim LstRw As Long
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Me.ID = Cells(LstRw, "A").Value + 1
    End Sub

    Tuesday, January 30, 2018 7:56 AM
  • Try using the initialize event - but specify the name of the worksheet that you are using: if it is not Sheet1 (like I've used), then change it in  the code. You can also control the formatting of the number, if that is important, using code like the commented-out line.

    Private Sub UserForm_Initialize()
        Me.ID.Text = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Value + 1

        'Me.ID.Text = Format(Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Value + 1,"00.0")

    End Sub

    Tuesday, January 30, 2018 1:47 PM
  • Your code works very well but I have an issue. When I have data inside the table the code doesn't work..

    For instance when the last empty row of column A is 10 the Userform appears the number 1 instead of number 11

    When I apply the code at normal cells (no table) the code works perfect.



    Tuesday, January 30, 2018 4:16 PM
  • If you have a table that could have empty rows at the bottom, then use code like

    If Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Value <>  "" Then

    Me.ID.Text = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Value + 1


    Me.ID.Text = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).End(xlUp).Value + 1

    End If

    If your column A is filled with formulas that could return a "" value, like:


    then you need to step through your cells to find the correct value, or use a table that auto-sizes based on other entries.

    Tuesday, January 30, 2018 5:28 PM
  • Well, It works but something strange happens. I have assign a macro into a button to show the UserForm, but when the UserForm appears it shows nothing. When I click on the surface of the UserForm the correct number appears. It sounds strange.. Its like the code doesn't run while the Userform opens..

    As you can see from the screenshot below the text box appears nothing while it opens..

    After I click on the UserForm  it appears the correct number.

    Userform Code

    Thanks for the help

    Tuesday, January 30, 2018 8:29 PM
  • I forgot to use the  initialize event, it works great now!!!

    Thanks for  help!!

    Tuesday, January 30, 2018 8:33 PM
  • Great - I was worried there for a second!
    Tuesday, January 30, 2018 10:24 PM
  • Hi again :)

    I have a list of parts (around 1000) and I have create a drop down list with combo-box. How can I filter the drop down list of combo-box while I am typing? For Instance when I type the letter "b" the drop-down list of combo-box will show all the parts which start with "b". When I type "ba" will show all the part starts with "ba" etc.


    Friday, February 2, 2018 5:54 AM
  • This works much better visually with a listbox - change your ComboBox to a Listbox.

    Add TextBox1 to your userform for the typing - the code will update the listbox selection as you type.  You can either remove values (the commented out section) or just change the selected value for the listbox (which should have a sorted list if you use the selection version instead of the deletion version). Change "List" to the name or address of you list of parts.

    Private Sub UserForm_Initialize()
      With ListBox1
        .List = Range("List").Value
      End With
    End Sub

    'Remove values that don't match

    'Private Sub TextBox1_Change()
    '  Dim x As Long, z
    '  ListBox1.ListIndex = -1
    '  For x = ListBox1.ListCount - 1 To 0 Step -1
    '     If LCase(Left(ListBox1.List(x), Len(TextBox1.Text))) <> _
    '    LCase(TextBox1.Text) Then ListBox1.RemoveItem x
    '  Next x
    '  ListBox1.ListIndex = 0
    'End Sub

    'Just select the  value as you type - good if your list is sorted

    Private Sub TextBox1_Change()
      Dim x As Long, z

      ListBox1.ListIndex = -1

      For x = 0 To ListBox1.ListCount - 1
        ListBox1.ListIndex = x
        z = ListBox1.Text
        If LCase(Left(ListBox1.Text, Len(TextBox1.Text))) = _
        LCase(TextBox1.Text) Then
            ListBox1.ListIndex = x + Application.Min(20, ListBox1.ListCount - x - 1)
            ListBox1.ListIndex = x
            Exit Sub
        End If
      Next x
    End Sub

    Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    MSForms.ReturnInteger, ByVal Shift As Integer)
      If KeyCode = 13 Then TextBox1.Text = ListBox1.Text
    End Sub

    Friday, February 2, 2018 2:41 PM
  • Hi, thanks for your answer,

    I've got some error using "Remove Values" method but your thought to use list-box is correct. I used this code and work pretty good.

    Private Sub UserForm_Initialize()
    End Sub
    Private Sub TextBox24_Change()
    Me.TextBox24.Text = StrConv(Me.TextBox24.Text, vbProperCase)
    Dim i As Long
    On Error Resume Next
    For i = 1 To Application.WorksheetFunction.CountA(Sheet3.Range("A:A"))
    a = Len(Me.TextBox24.Text)
    If Left(Sheet3.Cells(i, 1).Text, a) = Left(Me.TextBox24.Text, a) Then
    Me.ListBox1.AddItem Sheet3.Cells(i, 1).Value
    Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet3.Cells(i, 2).Value
    End If
    Next i
    End Sub

    Do you how can I display the selected value of the Listbox1 into TextBox23?

    I use Textbox23 to search into the Listbox1 and I want, while I am selecting the desired Item to be transferred into TextBox23. 

    Friday, February 2, 2018 11:50 PM