Method range of object failed

**Aland2929** · 04-01-2013, 12:50 PM

I am now getting this formula in the cell range?
=VLOOKUP(AT3,Range("J3:J" & LastRow - 1),8,FALSE)
Thanks in advance

**Aland2929** · 04-01-2013, 01:12 PM

The formula reflects #NAME? in the cells. It is not selecting my LastRow range?
Thanks in advance

**Aland2929** · 04-01-2013, 01:36 PM

I changed the code as follows but get the following error: [Object variable or With block Variable not set]:

LookupRange = Range("J3:J" & LastRow - 1)
Range("AW3:AW" & SecondLastRow - 1).Value = "=VLOOKUP(AT3,LookupRange,8,FALSE)"

Thanks in advance

**Aland2929** · 04-01-2013, 01:59 PM

This is the code I am using but the formula in the cell uses [LookupRange] instead of the range J3:J2565?

Dim LookupRange As Range
Set LookupRange = Range("J3:J" & LastRow - 1)
Range("AW3:AW" & SecondLastRow - 1).Value = "=VLOOKUP(AT3,LookupRange,8,FALSE)"

Thanks in advance

**Aland2929** · 04-02-2013, 03:44 AM

I would really appreciate some help as I have been struggling to resolve without success.

Thanks in advance.

**AndyLitch** · 04-02-2013, 05:17 AM

The Original problem where it was posting a NAME error was because the function was being entered into the worksheet like :
=VLOOKUP(AT3,Range("J3:J" & LastRow - 1),8,FALSE)
The problem there is with LastRow which is only applicable in the VBA script NOT the worksheet.
The second mistake was in the use of quotation marks
Third mistake was in your VLookup - you only looked up a single column but wanted a return from the 8th column
4th was to specify a worksheet range in the worksheet using VBA syntax

Incorrect
Range("AW3:AW" & SecondLastRow - 1) = "=VLOOKUP(AT3,Range(""J3:J"" & LastRow - 1),8,FALSE)"

Correct
Range("AW3:AW" & SecondLastrow - 1) = "=VLOOKUP(AT3,(J3:U" & LastRow - 1 & "),8,FALSE)"

Don't forget to Dim Lastrow and assign a viable value to it