CODEWITHSUNDEEP
excelvba
Ryan Huang
Ryan Huang

Reputation: 3

Excel VBA Search columns of long text string and replace characters within it

I've looked at all the examples and answers on this site and came up with this little program below. However, instead of replacing only the two lines that contains "802722AA", it replaces everything else that does NOT contain the string as well. I am out of ideas as to why this happens. Any help is appreciated.

Sub FindAndReplaceAGF()

Dim Loc As Range
Dim RowNum As Integer
Dim LastRow As Long

    With Sheets(2).UsedRange
    Range("A1").Select
        Set Loc = .Cells.Find(What:="802722AA")
        If Not Loc Is Nothing Then
            Do
                Loc.EntireRow.Select
        Selection.Replace What:="AGF", Replacement:="AGN", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
                RowNum = Loc.Row
                Set Loc = .FindNext(Loc)
            Loop While Loc.Row > RowNum
        End If
    End With
    Set Loc = Nothing

My data looks like this, I'm just redacting it because it's a 400+ line list. 

802722AA SANTA ROSA LEASING LLC2    AGF
59511VAA MICRON SEMICONDUCTOR       AGF
973204AA WINDERMERE AVIATION LLC    AGY
92242VAB VCK LEASE SA               AGF
68839PAB OSPREY AIRCRAFT LEASING    AGF
79977TAC SANDALWOOD 2013 LLC        AGN
02154QAA ALTITUDE INVEST 16 LLC     AGF
802722AA SANTA ROSA LEASING LLC     AGF
45170$88 ILLINOIS FACILITIES FUND   ACM

Note that this is a text file all dumped into column A in Excel. I cannot text to column it and I cannot do any fancy formulas whatsoever because it feeds to a proprietary program that will refuse the input. Note that there are a few AGF there and I only want it changed to AGN if it contains the text 802722AA.

Upvotes: 0

Views: 916

Answers (2)

user3598756
user3598756

Reputation: 29421

For data in column "A" with header in row 1, you can use this code:

Option Explicit

Sub Main()
    With Worksheets("MySheetName")
        With .Range("A1", .Cells(.Rows.Count,1).End(xlUp))
            .Autofilter field:=1, Criteria1:="802722*"
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then . Replace "AGF", "AGN"
        End With
        .AutoFilterMode = False
    End With
End With

Upvotes: 1

Matt Cremeens
Matt Cremeens

Reputation: 5151

If it were me, and this may not be all that efficient, I think I'd make use of the split function like this

finalRow = cells(65000,1).end(xlup).row 'get the last row of column A
for i=2 to finalRow 
    parts = split(cells(i,1), " ") 'make an array out of the parts separated by spaces
    newString = ""
    if parts(0) = "802722AA" and parts(ubound(parts))="AGF" then 
       for j=lbound(parts) to ubound(parts) - 1 'get all but the last part
           newString = newString & " " & parts(j) 'make a new string of all but the last part
       next j
       newString = newString & " AGN" 'at the end, replace the AGF with AGN
    end if
    cells(i,1)=newString 'replace contents of cell with our newly constructed string
next i

Upvotes: 1

Related Questions