CODEWITHSUNDEEP
excelvba
MikeV
MikeV

Reputation: 1

Find and replace text

I'm new to VBA in Excel and have a fairly simple issue. I need to search for specific text string Bath in Column B. If it exists, then I need to replace specific text string KCab in Column C of the same row with BCab3. I need to run or loop this until it checks all the cells in Column B.

Here is what I have so far, which is not working:

Sub Correct_Attribute_Suffix()

    Dim c As Range
    Dim SrchRng

    Set SrchRng = ActiveSheet.Range("B3", ActiveSheet.Range("B65536").End(xlUp))
    Do
        Set c = SrchRng.Find("BATH", LookIn:=xlValues)
        If Not c Is Nothing Then Currentcell.Offset(, 1).Value = Replace("KCab", "K", "B")

    Loop While Not c Is Nothing

End Sub

Upvotes: 0

Views: 70

Answers (1)

user4039065
user4039065

Reputation:

This is just a Find/FindNext operation using offset and replace to accomplish your attribute suffix adjustment.

Sub Correct_Attribute_Suffix()
    Dim fnd As Range, addr As String

    With ActiveSheet.Range("B3", ActiveSheet.Range("B65536").End(xlUp))
        Set fnd = .Find(What:="BATH", After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
        If Not fnd Is Nothing Then
            addr = fnd.Address
            Do
                fnd.Offset(0, 1) = Replace(fnd.Offset(0, 1).Value2, "KCab", "BCab3", 1, 1, vbTextCompare)
                Set fnd = .FindNext(After:=fnd)
            Loop While addr <> fnd.Address
        End If
    End With
End Sub

Upvotes: 1

Related Questions