| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup error
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! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP IS ERROR | Excel Worksheet Functions | |||
| VLOOKUP Error | Excel Discussion (Misc queries) | |||
| vlookup error | Excel Worksheet Functions | |||
| vlookup() error #n/a | Excel Worksheet Functions | |||
| using vlookup, but error #N/A | Excel Programming | |||
Threaded Mode
