try:

JWS.Range("BL4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-1]," _
& level4.address(external:=true, referencestyle:=xlR1C1) & _
",2,FALSE)"

On 09/28/2011 12:03, wesley holtman wrote:
Hello,

I ran a problem with one of my Vlookup formulas. The formula is a
small piece of a fairly large macro so I posted just the code that
pertains to the formula in question. The formula was working great
until I added a row to the lookup array. I thought by redefining the
lookup array every time I run the macro, I should be able to add to
the array without any trouble. The formula is working all other values
but the row I added which is returning a #NA error.

Does anyone know why this formula is error prone?

Set Level41 = Worksheets("level 4 Sum")

'finding final row
LFinalRow = Level41.Cells(Rows.Count, 1).End(xlUp).Row

'finding final column
LFinalCol = Level41.Cells(1, Columns.Count).End(xlToLeft).Column

'setting up lookup array
Set Level4 = Level41.Cells(1, 1).Resize(LFinalRow, LFinalCol)

'the lookup form that will not work
JWS.Range("BL4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-1],level4,2,FALSE)"

--
Dave Peterson