Runtime Error 91 : Object variable or With block variable not set

[mars07]

Hi

I am getting Run time Error 91 on spread where I am setting my range.

I used exactly same code (100% same), just changed the name in another program and it was working fine. I dont know whats wrong here...

Only difference is, in that program, a button was activating the procedure and and in this program I want the "Exit" event to trigger the procedure... Can some one tell me, what going wrong ?

Dim Code As Variant
Dim spread As Range
Dim looper As Long
Static flagger As Long
spread = "A1:Z99"

 

[VoG]

Try

Dim Code As Variant
Dim spread As Range
Dim looper As Long
Static flagger As Long
Set spread = Range("A1:Z99")

 

[mars07]

In that case it is giving Type Mis-Match error

 

[Norie]

Is that all the code?

You mention an Exit event is triggering this, can you post all the code?

 

[mars07]

Private Sub Supplier_Code_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Code As Variant
Dim spread As Double
Dim looper As Long
Static flagger As Long
Set spread = ("A1:Z99")

If Me.Supplier_Code = "" Then
MsgBox ("Supplier Code Must be entered")
Cancel = True
Me.Supplier_Code.SetFocus
End If

For looper = 1 To 100

    If (Sheets("Suppliers").Cells(c, 1).Value = Me.Supplier_Code) Then
    flagger = flagger + 1
    End If
Next looper

If (flagger > 0) Then
    Cancel = True
    Sheets("Suppliers").Activate
    Me.Pro_Code = WorksheetFunction.VLookup(Me.Supplier_Code.Value, Range(spread), 5, False)
    Me.Pro_Details = WorksheetFunction.VLookup(Me.Supplier_Code.Value, Range(spread), 6, False)
    Me.Qty_Avail = WorksheetFunction.VLookup(Me.Supplier_Code.Value, Range(spread), 8, False)
    Me.Cost_Unit = WorksheetFunction.VLookup(Me.Supplier_Code.Value, Range(spread), 7, False)
    Me.Total_Cost = WorksheetFunction.VLookup(Me.Supplier_Code.Value, Range(spread), 10, False)
Else

    If (MsgBox("Supplier does not Exist, Do you want to Check again", vbYesNo) = vbYes) Then
    Cancel = True
    Me.Supplier_Code = ""
    Me.Supplier_Code.SetFocus
    Else
    Unload Me
    End If
    
End If
End Sub

 

[mars07]

also to mention that exact same code was working for me in a search button in another program

 

[Norie]

Did you actually make the change that VoG suggested?

 

[mars07]

you can see it in the code for ure self it is there... prior to change it was simply

spread = "A1:Z99"

i really cant understand... why it is working in one program perfectly ok and why on here... is it linked to the fact that there i am using button and this is and exit event function ?

 

[mars07]

If I define

Dim Spread as Double

It gives error "Object Required"

and if I define

Dim Spread as Range

it says Type Mis-Match .... In my last program i used it as double though

 

[Norie]

You have this in your code:

Set spread = ("A1:Z99")

Where's the Range("A1:Z99") that was suggested?

Also is spread meant to be a reference to a range?

If it is then you shouldn't need to wrap it in Range in the Worksheet.Function.Vlookup later in the code.