Vlookup error

[wesley holtman]

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)"

 

[Javed]

The second argument of vlook needs address of range not range itself
Try
"=VLOOKUP(RC[-1],level4.Address,2,FALSE)"

 

[Dave Peterson]

try:

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

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)"

 

[wesley holtman]

try:

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

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)"

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

 

[Dave Peterson]

The .address property gives a variant of $A$1 or R1C1 -- depending on what all
those other parameters are given.

external:=true will include the workbook name and worksheet name.
referencestyle:=xlr1c1 will use R1C1 reference style (you'll want to match the
..formulaR1C1 (or .formula) that you're using.

There are other options, too. See VBA's help for lots more info.

try:

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

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)"

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

 

[wesley holtman]

The .address property gives a variant of $A$1 or R1C1 -- depending on what all
those other parameters are given.

external:=true will include the workbook name and worksheet name.
referencestyle:=xlr1c1 will use R1C1 reference style (you'll want to match the
.formulaR1C1 (or .formula) that you're using.

There are other options, too.  See VBA's help for lots more info.

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)"

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

Thanks Dave!