Lookup as Part of Macro

[Ken]

We have a large macro. Within it we are trying to use
VLOOKUP. The data we want to look-up is in workbook
SOA_V1.0.xls. Data in that workbook is in columns B-H with
a varying number of rows.

The value used to do the look-up is in column E in FMS.csv
which has several hundred rows.

We want to enter a VLOOKUP formula in FMS.csv in G2 and
another VLOOKUP in H2 and then copy these formulas down
through the rest of the rows in columns G and H.

We can not get the syntax right and hope someone can
assist.

Is line 3 the way to assign a name (ACC) to the lookup
range using the Numrows variable?
Lines 5 and 6 don't work, so something is wrong with that
code.
Will lines 7-11 correctly copy the formulas down the rest
of the rows?

TIA.

1. Windows("SOA_V1.0.xls").Activate
2. NumRows = Range("A1").End(xlDown).Row
3. ActiveWorkbook.Names.Add Name:="ACC",
RefersTo:="Sheet1!" & Cells(2, 2) & ":" & NumRows
4. Windows("FMS.csv").Activate
5. Range("G2").Formula = "=VLOOKUP(E2,'SOA_V1.0.xls!
ACC',2,FALSE)"
6. Range("H2").Formula = "=VLOOKUP(E2,'SOA_V1.0.xls!
ACC',3,FALSE)"
7. Range("G2:H2").Select
8. Selection.AutoFill Destination:=Range("G2:H" &
NumRows), Type:=xlFillDefault
9. Columns("G:H").Select
10. Selection.Copy
11. Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= False, Transpose:=False

 

[Frank Kabel]

Hi
try
5. Range("G2").Formula = "=VLOOKUP(E2,'SOA_V1.0.xls'!
ACC,2,FALSE)"
6. Range("H2").Formula = "=VLOOKUP(E2,'SOA_V1.0.xls'!
ACC,3,FALSE)"

changing the location of the ending apostrophe