creating a vlookup using a macro

  • Thread starter Thread starter Legacy 93538
  • Start date Start date
L

Legacy 93538

Guest
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.

Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try changing .FormulaR1C1 to .Formula.

Why are you looking up A:AB but only looking in column A (3rd parameter of 1)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
11
22
33
44
55
Sheet2



Excel Workbook
AB
133
Sheet1
 
Upvote 0
Hi

Thanks for all your help

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

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top