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"

 

[mars07]

Dim Code As Variant
Dim spread As Double
Dim looper As Long
Static flagger As Long
[B][COLOR="Red"]Set spread = ("A1:Z99")[/COLOR][/B]

Spread refers to a range in Supplier sheet... While using Vlookup function if I type the code as

worksheetfunction.vlookup(Lookup Value, Range ("A1:Z99"), Column Index, True/False)

It gives error... I read some where that range has to be defined as a variable and then given as

Range = "A1:Z99"
worksheetfunction.vlookup(Lookup Value, Range (Range), Column Index, True/False)

 

[mars07]

OK

Dim spread as Range

spread = Range ("A1:Z99")

Now it is not giving error, but now there is problem with Vlookup function... let me see wht it is and I'll get back to u guys... thanks for all the help

 

[Cloverken]

As VoG said you should have

Dim spread as Range

and also use "Set" as VoG mentioned since you are establishing an Object and without Set you will get the "Object variable or with block variable not set" error

Set spread as Range("A1:Z99")

Then within your Vlookup code it should look like this

WorksheetFunction.VLookup(Me.Supplier_Code.Value, spread, 5, False)

 

[mars07]

thanks man... I am not a programmer basically... but I guess with you guys around it will not be hard to become one

 

[Cloverken]

I am not a programmer either. I just keep hacking away at it.

VoG and Norie are both exceptional experts.

Keep going, there are no silly questions.