VBA Find value, insert row above, format

[exo33]

Hello All,

I found below code on the forum and modified it slightly. Sadly, I keep having issues with it. Long story short, I have a set of text values in column I. I want Excel to find the first value "Good", insert a blank row right above it, then go 4 cells to the left and label it "Above good".
For example:
If "Good" value is found in cell I200, I want a new row inserted - row 199 and "Above good" text in cell E199. If no "Good" values are found in column I, do nothing.

The issue I'm having is that sometimes value "Good" will pop up in column E and Excel will insert row above it, instead of searching in column "I" ONLY.

Sub EETC()Sheets("Template").Select
    Dim cl As Range
    With Worksheets("Template").Cells
        Set cl = .Find("Good", After:=.Range("I2"), LookIn:=xlValues)
        If Not cl Is Nothing Then
            cl.Select
        End If
    End With


    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -4).FormulaR1C1 = "Above good"
    ActiveCell.Offset(0, -4).Select
    Selection.Font.Bold = True



End Sub

 

[JoeMo]

Change this:

With Worksheets("Template").Cells

To this:

With Worksheets("Template").Range("I:I")

 

[exo33]

Change this:

With Worksheets("Template").Cells

To this:

With Worksheets("Template").Range("I:I")

Runtime error '13' Type mismatch

Sub EETC()

Sheets("Template").Select
    Dim cl As Range
    With Worksheets("Template").Range("I:I")
        [COLOR=#ff0000][B]Set cl = .Find("Good", After:=.Range("I2"), LookIn:=xlValues)[/B][/COLOR]
        If Not cl Is Nothing Then
            cl.Select
        End If
    End With


    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -4).FormulaR1C1 = "Above good"
    ActiveCell.Offset(0, -4).Select
    Selection.Font.Bold = True



End Sub

 

[JoeMo]

Change the line that causes the error to this:

 Set cl = .Find("Good", After:=.Cells(2), LookIn:=xlValues)

 

[exo33]

Change the line that causes the error to this:

 Set cl = .Find("Good", After:=.Cells(2), LookIn:=xlValues)

Now it inserts empty row above a cell I had selected prior to running the macro. i.e. if I click on cell D12 before running macro, new row 11 is created above it.

 

[exo33]

Fixed by simplifying the code (see below). I'm assuming that using "Select" is not the best practice?

Sub Macro1()
'
' Macro1 Macro
Sheets("Template").Select
Dim cl As Range
Set cl = Range("I:I").Find("Good")
If Not cl Is Nothing Then cl.Select
    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -4).FormulaR1C1 = "Above good"
    ActiveCell.Offset(0, -4).Select
    Selection.Font.Bold = True
End Sub