CODEWITHSUNDEEP
vbaexcel
m36
m36

Reputation: 15

Run-time error '91'; Object variable or With block variable not set

Im getting the following error:

"Run-time error '91'; Object variable or With block variable not set"

From this particular code written in method "CreateArray()", when it's called from within the NewEntry() method:

    With NameStart
    n = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
    End With

Original Code:

Sub CreateArray()

With NameStart
    n = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
End With

ReDim PhoneName(n)

For i = 1 To n
    PhoneName(i) = NameStart.Offset(i, 0)
    PhoneNumber(i) = NumStart.Offset(i, 0)
Next i
End Sub

Next method:

Sub NewEntry()
Application.ScreenUpdating = False
Worksheets("Phone Data").Activate

NewName = InputBox("Please enter the new entry name using the " _
& "following format: Last, First", "New Name", "Smith, John")
If NewName = "" Then
    MsgBox "Please enter a name."
    Exit Sub
End If

NewNumber = InputBox("Please enter the 10-digit phone number for " _
        & NewName & " usin the following format: 1234567890", _
        "New Number", 1234567890)
        If NewNumber / 10 ^ 10 < 0.1 Or NewNumber / 10 ^ 10 > 1 Then
            MsgBox "Please enter a 10-digit number."
            Exit Sub
        End If
        Call CreateArray
            For i = 1 To n
                If PhoneName(i) = NewName Then
                MsgBox "There is already an entry for this person in the " _
                & "phone book."
                Exit Sub
            End If
        Next i

    NameStart.Offset(n, 0).Value = NewName
    NumStart.Offset(n, 0).Value = NewNumber
    Range(NameStart, NumStart.Offset(n, 0)).Select
        Selection.Sort Key1:=NameStart, Order1:=xlAscending
    Worksheets("Phonebook Welcome").Activate
        Application.ScreenUpdating = True
        MsgBox NewName & " has been added to the phone book."
End Sub

Variable Declaration:

Public i As Integer
Public n As Integer
Private PhoneName() As String
Private PhoneNumber() As Double
Public NewName As String
Public NewNumber As Double
Public NameStart As Range
Public NumStart As Range

Upvotes: 0

Views: 750

Answers (1)

barrowc
barrowc

Reputation: 10679

Declaring Public NameStart As Range doesn't actually make NameStart refer to any actual range on any worksheet. Before you can use can call methods on NameStart, you need to set it to refer to an actual range, like this:

Set NameStart = Worksheets("Sheet1").Range("A1:D10")

If you happen to have defined a named range called "NameStart" then you would still need to link that to the NameStart variable:

Set NameStart = Range("NameStart")

If you don't do this then NameStart isn't linked to anything and attempting to call methods on it will produce a run-time error

Upvotes: 1

Related Questions