vba vlookup & named range

[Joe Z]

I am having difficulty using vlookup in vba using FormulaR1C1. I am tring to lookup a value from a table using the table O_Rng located in a different sheet. Here is what I have:

Dim O_Rng as range

set O_Rng = Range("org_tbl!$A$2:$H$59")

sheets("Sheet3").cells(2,9).select
activecell.offset(4,0).FormulaR1C1= "=vlookup(RC[-7],org_tbl!O_Rng,2,false)

I get #Name error.
I have tried changing the lookup table value to

activecell.offset(4,0).formulaR1C1= 
"=vlookup(RC[-7],Worksheets(""org_tbl"").range(""$A$2:$H$59""),2,false)

with no luck, yet I can use

Activecell.offset(4,0) = application.worksheetfunction _
.vlookup(Activecell.offset(4,-7),worksheets("org_tbl").range("$A$2:$H$59"),2,false)

and it gives me the correct result.

Any suggestions, thanks in advance!

 

[Norie]

Joe

It's not particularly clear what you want to do but perhaps this might work.

Dim O_Rng As Range
Set O_Rng = Range("org_tbl!$A$2:$H$59")
Sheets("Sheet3").Cells(2, 9).Offset(4, 0).FormulaR1C1 = "=vlookup(RC[-7],org_tbl!" & O_Rng.Address(ReferenceStyle:=xlR1C1) & ",2,false)"

PS Why are you mixing up A1 and R1C1 notation? Wouldn't it just become confusing.

 

[Joe Z]

Joe

It's not particularly clear what you want to do but perhaps this might work.

Dim O_Rng As Range
Set O_Rng = Range("org_tbl!$A$2:$H$59")
Sheets("Sheet3").Cells(2, 9).Offset(4, 0).FormulaR1C1 = "=vlookup(RC[-7],org_tbl!" & O_Rng.Address(ReferenceStyle:=xlR1C1) & ",2,false)"

PS Why are you mixing up A1 and R1C1 notation? Wouldn't it just become confusing.

Thank you! You were right on. My problem was I did not realize that I needed to include the .address to the named range. I am very new to vba, hence the mixing od A1 R1C1 notation. It was the result of trying numerous scenarios and getting them all meshed together. Thanks.