Object variable or with block variable is not set

[kaiko_k]

I'm having a problem with a piece of code. I have searched and tried various solutions but nothing seems to work.
I also don't understand why sometimes this code works perfectly, for example when restarting excel and opening workbook again. And then after I have run few other codes (workbook contains many) I keep getting "Object variable or with block variable is not set" error.

Debug shows I have a problem with row "Worksheets("Leping").Range("A1:A10000").Find("lisa_kaasomanik", lookat:=xlPart).Activate"

Basically what I'm trying to do is to activate a certain cell and then insert new row according to active cell while copying formats from another row.

Can anyone please guide me to a solution?

Sub InsertRow()


Application.ScreenUpdating = False
ActiveSheet.Unprotect


Worksheets("Leping").Range("A1:A10000").Find("lisa_kaasomanik", lookat:=xlPart).Activate


       
    ActiveCell.Offset(0, 0).EntireRow.Insert
    ActiveCell.Offset(-1, 0).EntireRow.Copy
    ActiveCell.Offset(0, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    ActiveSheet.Protect


Application.ScreenUpdating = True


End Sub

 

[Takae]

If you use the Find statement, you must also consider the case where the search value was not found.

Sub InsertRowv()
Dim FC, Fcr As Long
Application.ScreenUpdating = False
Worksheets("Leping").Unprotect
Set FC = Worksheets("Leping").Range("A1:A10000").Find("lisa_kaasomanik", LookIn:=xlValues, lookat:=xlPart)
    If FC Is Nothing Then
        MsgBox "Not Found"
    Else
        Fcr = FC.row
        Rows(Fcr).EntireRow.Insert
        Rows(Fcr - 1).EntireRow.copy
        Rows(Fcr).EntireRow.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        Worksheets("Leping").Protect
        Application.ScreenUpdating = True
    End If
End Sub

And, the find statement inherits the search method that the user performed manually the next time.
The following code is without the find statement.

Sub test()
    Dim i As Long, j As Long
    Dim rng As Range, x
    
    Application.ScreenUpdating = False
    Worksheets("Leping").Unprotect
    
    With Sheets("Leping")
        Set rng = .Range("A1:A10000")
        x = rng
        For j = LBound(x) To UBound(x)
            If x(j, 1) Like "*lisa_kaasomanik*" Then
                .Rows(j).EntireRow.Insert
                .Rows(j - 1).EntireRow.copy
                .Rows(j).EntireRow.PasteSpecial Paste:=xlPasteFormats
                Application.CutCopyMode = False
                .Protect
                Exit For
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

The reason that it sometimes works & sometimes doesn't, is that you have only specified one of the parameters. Excel remembers these parameters every time you use Find.
e.g The original default (when you open xl) is MatchCase=False, but if you use Find with MatchCase=True, the default becomes True.
Whenever using Find, or Replace it is always a good idea to specify any parameters that may affect what you're looking for.