CODEWITHSUNDEEP
vbaexcelreplacefind
Leocodetwist
Leocodetwist

Reputation: 61

difficultly vba find replace

I am a little confused if my current method is the best, but I have excel files in which the first row has different column headers depending from whom I get them from. I am trying to standardize the headers in row 1.

How would I write a script to look for, lets say:

find = "clname" or "first name" or "full name"
replace = " Name"

find = "address" or "adr" or "location"
replace = "Cl_Adress"

I would need to do about 15 different finds and replace. Is there a better way to do it. I currently am working with this script. 

Sub findrep()
    Dim i As String
    Dim k As String

    i = "Find"
    k = "Text to replace"
    Rows(1).replace what:=i, replacement:=k, lookat:=xlPart, MatchCase:=False
End Sub

Upvotes: 1

Views: 97

Answers (2)

crookie
crookie

Reputation: 270

I'd do it using a regular expression. Set a reference to "Microsoft VBScript Regular Expressions 5.5" and then do this looping through the columns in your first row

Sub ReplaceMultipleItems() Dim rx1 As New RegExp Dim rx2 As New RegExp Dim intColCount As Integer Dim intColCounter As Integer

intColCount = ActiveSheet.UsedRange.Columns.Count

rx1.Pattern = "clname|first name|full name"
rx2.Pattern = "address|adr|location"

For intColCounter = 1 To intColCount
    ActiveSheet.Cells(1, intColCounter).Value = _
    rx2.Replace(rx1.Replace(ActiveSheet.Cells(1, intColCounter).Value, "Name"), "Cl_Adress")
Next intColCounter

End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

Use equally sized two variant arrays; one for the what:= values and another for the replacement:= values. Cycle through them to accomplish the replacements.

Sub blahblah()
    Dim v As Long, fnd As Variant, rpl As Variant

    fnd = Array("clname", "first name", "full name", _
                "address", "adr", "location")
    rpl = Array("name", "name", "name", _
                "Cl_Adress", "Cl_Adress", "Cl_Adress")

    With Worksheets("Sheet1")
        With .Rows(1)
            For v = LBound(fnd) To UBound(fnd)
                .Replace what:=fnd(v), replacement:=rpl(v), _
                         MatchCase:=False, lookat:=xlWhole
            Next v
        End With
    End With
End Sub

Upvotes: 2

Related Questions