CODEWITHSUNDEEP
vbaexcel
Jcmoney1010
Jcmoney1010

Reputation: 922

Searching for a sentence in a worksheet

I'm trying to write some VBA for an Excel file that looks for a specific phrase/sentence in the column header and changes that phrase/sentence if it finds it. The issue is that sometimes the file won't have the phrase I'm searching for, and the VBA throws an error. Here is my code:

Dim srch As Range

   srch = Cells.Find(What:="Usage Charge (Overage Charges)", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If Not srch Is Nothing Then
 ActiveCell.FormulaR1C1 = "Usage Group Overage"
  End IF

This works fine when "Usage Charge (Overage Charges)" exists in the worksheet, but if it doesn't then I get an error telling me the object doesn't exist.

Is there a way to get this simply to do nothing if that phrase doesn't exist?

Upvotes: 1

Views: 64

Answers (2)

Bas Verlaat
Bas Verlaat

Reputation: 852

the easiest way is to use an error handler:

Dim srch As Range

On error goto ErrH

 srch = Cells.Find(What:="Usage Charge (Overage Charges)", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If Not srch Is Nothing Then
ActiveCell.FormulaR1C1 = "Usage Group Overage"
End IF

Exit sub

ErrH:
  ' Do nothing

End sub

Upvotes: 2

user4039065
user4039065

Reputation:

Here are a few choices. The first two use wildcards but only replace 1 instance. They would have to be looped. The third replaces all byt depends upon an exact match (no xlPart).

Sub optimal()
    Dim srch As Range, str As String, rpl As String

    str = "Usage Charge (Overage Charges)"
    rpl = "Usage Group Overage"
    With Worksheets("Sheet1")
        'check if it exists using COUNTIF with silcards
        If CBool(Application.CountIf(.Rows(1), Chr(42) & str & Chr(42))) Then
            'replace 1 occurance value directly
            .Cells(1, Application.Match(Chr(42) & str & Chr(42), .Rows(1), 0)) = rpl
        End If

        'check if it exists using MATCH with silcards
        If Not IsError(Application.Match(Chr(42) & str & Chr(42), .Rows(1), 0)) Then
            'replace 1 occurance value directly
            .Cells.Find(what:="Usage Charge (Overage Charges)", After:=ActiveCell, _
                        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) = rpl
        End If

        'use if "Usage Charge (Overage Charges)" is the entire cell content
        'replaces all occurances
        .Rows(1).Replace what:=str, replacement:=rpl
    End With

End Sub

Upvotes: 0

Related Questions