CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

Using an If macro to find cell with certain text

I have a cost sheet with a lot of Macros in it. One of the Macros is to add a new line item to the bottom of the sheet. I want to enter an If code saying that if the item I add starts with the text "Custom " with a space after I want it to find that cell and select it. Below is the code I am trying but it Debugs with Type Mismatch and highlights the Custom = Range("B:B").Value line. Any help is much appreciated.

 Dim Custom As String
    Custom = Range("B:B").Value
    If Custom Like "Custom *" Then
    Cells.Find(What:="Custom ", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    ActiveCell.FormulaR1C1 = ("Test")
    End If

Upvotes: 3

Views: 70877

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

You do not need to use LIKE for this. Directly use .Find See this example

Note the use of LookAt:=xlPart. This will ensure that if there is "Custom " anywhere in the cells content, the code will catch it.

Sub Sample()
    Dim ws As Worksheet
    Dim aCell As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set aCell = .Columns(2).Find(What:="Custom ", LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Value = "Test"
        Else
            MsgBox "Not Found"
        End If
    End With
End Sub

Upvotes: 10

Related Questions