CODEWITHSUNDEEP
excelvba
cmp119
cmp119

Reputation: 1

1 Column find and replace

Column N contains the following information:

Doe, Jane
Doe, John
Doe, Jr., Johnny

I am using the following Excel VB for Applications code:

Columns("N").Replace What:=", Jr.,", _
                          Replacement:=" Jr.,", _
                          LookAt:=xlpPart, _
                          SearchOrder:=False, _
                          MatchCase:=True, _
                          SearchFormat:=False, _
                          ReplaceFormat:=False

It works fine as long there is a name that contains ", Jr.,". If the columns does not contain this value, I receive Run-time error "9": subscript out of range.

What can I do to fix this issue?

Upvotes: 0

Views: 43

Answers (1)

user4039065
user4039065

Reputation:

The absence of something to replace should not throw an error. The code should be able to be run against a completely blank column.

With ActiveSheet
    With .Columns("N")
        .Replace What:=", Jr.,", Replacement:=" Jr.,", LookAt:=xlPart
    End With
End With

Note that your original sample contained xlpPart in place of xlPart.

Upvotes: 1

Related Questions