XL2003 VBA: Run-time error '91' When Insert Rows using a macro

[TechTank]

Hi All,

I've been using the following code successfully and this is it's function:
It copies some values from one sheet to another, then looks for a value (0,1 or 2) in Column A of the ("Environment Information") worksheet and then copies a specified row from the ("Format Control"). sheet under the row it found this value in last on the ("Environment Information") worksheet. :

Option Explicit
Sub Add_Ad_Hoc_Server()
Dim rng As Range
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    Sheets("Format Control").Range("C4").Value = _
        Sheets("Cover Sheet").Range("B27").Value
    With Sheets("Environment Information")
        .Unprotect
        Set rng = .Columns("A").Find(What:="0", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
        Sheets("Format Control").Rows(4).Copy
        rng.Offset(1).EntireRow.Insert
        rng.Offset(1, 3).Select
 
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowInsertingRows:=True, AllowDeletingRows:=True
    End With
 
    ActiveWindow.ScrollRow = 1
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I now want to adapt this code to use on another sheet, simply to look for the last value of "3" in column A on the active sheet, when it's found copy row 19 from the ("Format Control") worksheet and paste it one row below the row that had the value "3" in it. When I use the adapted code below I seem to get this error:

Microsoft Visual Basic
Run-time error '91':
Object variable or With block variable not set

Adapted code with code that becomes highlighted when debugging in VBA in bold:

Option Explicit
Sub Add_Line_Pre_Deployment_Preparation()
Dim rng As Range
 
'Application.DisplayAlerts = False
'Application.ScreenUpdating = False
 
    With Sheets("Release Plan Ver Draft 0.1")
        .Unprotect
        Set rng = .Columns("A").Find(What:="3", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
        Sheets("Format Control").Rows(19).Copy
        [B]rng.Offset(1).EntireRow.Insert[/B]
        rng.Offset(1, 3).Select
 
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowInsertingRows:=True, AllowDeletingRows:=True
    End With
 
'    ActiveWindow.ScrollRow = 1
'Application.DisplayAlerts = True
'Application.ScreenUpdating = True
End Sub

I'm thoroughly stumped as this code was provided by another member on here and I thought I had it understood but it would seem this has confused me.

Any help would be gratefully appreciated. I'm looking for someone to point out my mistake and highlight how I can resolve to I can learn as well as fix my code

Thank you for your time.

Mark.

 

[TechTank]

*Bumpage*

 

[TechTank]

Solution Created:

I managed to piece this solution together from the following webpage:

http://www.rondebruin.nl/find.htm

and the code is as follows:

Option Explicit
Sub Insert_Line_Under_Value_Found()
    Dim FindString As String
    Dim Rng As Range
    
'Enter Search Value between the ""
    FindString = "1.1"
    
    If Trim(FindString) <> "" Then
 
'Set The Search Range Between The ""
        With ActiveSheet.Range("A:A")

            Set Rng = .Find(What:=FindString, After:=.Cells(1, 1), LookIn:=xlValues, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False)
 
'Moves To The Cell With The Value In
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
 
'Copys The Row Specified
                Sheets("Format Control").Rows(19).Copy
                
'Inserts The Row Copied Above Beneath The Row Found With The Value In
                Rng.Offset(1).EntireRow.Insert
 
'Moves The Active Cell 1 Row Down and 2 Cells To The Right
                Rng.Offset(1, 2).Select
                
                Else
            End If
        End With
    End If
End Sub

This will find the LAST value specified in the 'FindString = "" in Column A ONLY and then copy and insert a row from the sheet called "Format Control" underneath the row with the value specified. . The row it is copying contains the value "1.1" for my workbook so that the next time the code is executed it will copy and insert the row beneath the last one and so on. It will then move the Active Cell 1 Row down and 2 Cells in onto the new row that has just been inserted.

Hope this helps anyone looking for this solution.

Mark.