creating a vlookup using a macro

[Legacy 93538]

Hi

I have created a macro which opens a workbook, creates a new sheet called "Values" and copies the forumla data from one sheet and paste special into the Values sheet. It then creates a vlookup in cell B1 on sheet Input_Reference_Table that lookups the value in cell A1 within a table on the "Values" sheet and returns the values in column 1 within the table range.

Option Explicit
Sub PP3InputRef()
Dim StrFldr As String
Dim PPWB As Workbook
Dim Nrow As Long
Dim cell As Range
Application.DisplayAlerts = False
StrFldr = ThisWorkbook.Path
Set PPWB = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_Input_Reference_Table_V1.xlsx")

PPWB.Sheets.Add.Name = ("Input_Reference_Table"): PPWB.Sheets.Add.Name = ("Values")
 
PPWB.Sheets("PPIIIFORM").Select: Cells.Select: Selection.Copy: PPWB.Sheets("Values").Select: Cells.Select: Selection.PasteSpecial Paste:=xlPasteValues

PPWB.Sheets("Input_Reference_Table").Select: Range("B2").Select: ActiveCell.FormulaR1C1 = "=VLOOKUP(A1,Values!A4:AB700,1,FALSE)"

However when the macro runs i get an error in B1 cell of "#NAME??" i'm not sure why as the value in the cell is in the table range.

Can anyone help me?

Thanks

Jessicaseymour

 

[jim may]

What happens if you use:

ActiveCell.Formula

versus

ActiveCell.FormulaR1C1

 

[VoG]

Try changing .FormulaR1C1 to .Formula.

Why are you looking up A:AB but only looking in column A (3rd parameter of 1)?

 

[Legacy 93538]

Hi

I have tried changing it to ActiveCell.Formula but its stillputs an error in the cell but its changed to "#N/A"

The reason for the large table range is because the value in cell A1 is the large cell range next to column A. The value is cell A1 is in the range F4:U700 and the column it looks up is A so it needs to be a large table range. Also the range F4:U700 might have columns added so the ranges goes to column AB to include the fact it might change.

 

[VoG]

What happens if you enter the formula manually? Does it still return #N/A?

If so, things to check: http://www.contextures.com/xlFunctions02.html#trouble

 

[Legacy 93538]

Hi

When i try manually it does the same thing.... so not sure as the value is the table range so it should find the value and get the value in corresponding cell in column A.

 

[VoG]

Probably you have text in one sheet and numbers in the other (see link above).

I don't see what you are hoping to achieve by a one column lookup. Surely that will just return the exact same value: see below

Excel Workbook
	A
1	1
2	2
3	3
4	4
5	5
Sheet2

Excel Workbook
	A	B
1	3	3
Sheet1

 

[Legacy 93538]

Hi

Thanks for all your help

I figured out what it was i was looking in the wrong column. Thanks for all you help