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

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

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

On Sep 29, 8:29*am, Dave Peterson wrote:
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- Hide quoted text -

- Show quoted text -

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

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.

On 09/29/2011 08:35, wesley holtman wrote:
On Sep 29, 8:29 am, Dave wrote:
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- Hide quoted text -

- Show quoted text -

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

--
Dave Peterson

On Sep 30, 7:33*am, Dave Peterson wrote:
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.

On 09/29/2011 08:35, wesley holtman wrote:





On Sep 29, 8:29 am, Dave *wrote:
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- Hide quoted text -

- Show quoted text -

IT WORKED! Thanks Dave and Jave you guys rock!

What exactly does ".address" do?

--
Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave!