Solved: Using VLOOKUP in VBA Code

[Glaswegian]

Hi again

I have 2 workbooks. One contains a list of Managers and their assigned code numbers. The other is received monthly and contains data relating to monthly performance. In many cases, the monthly data contains the correct Manager code but not the Manager name. The Manager name needs to be populated.

Most of my code works fine. Using the Manager list, I can open the relevant monthly workbook etc, but I can't seem to get my Vlookup to work.

The Manager name appears in Column F and the code appears in Column E, both in the Monthly Data workbook. In the Manager code workbook, the list is a named range called MainList. This list can be added to or deleted from and I have code running that sorts and renames the table.

The problem, I think , is in referencing the named range in the Manager code workbook.

Set wbkMonthly = Workbooks.Open(myPath & "Completion and Doc Fees" & " " & strMonth & ".xls")
finalRow = wbkMonthly.ActiveSheet.Range("F65536").End(xlUp).Row
For x = 2 To finalRow
    Cells(x, 6).Value = WorksheetFunction.VLookup(Range("F" & x), "MainList", 2, False)
Next x

The above code is run from a button on the Manager code workbook.

Any help much appreciated.

[Bob Phillips]

Try

[vba]

Cells(x, 6).Value = Application.VLookup(Range("F" & x), Range("'Manager.xls'!MainList"), 2, False)
[/vba]

[Glaswegian]

Thanks xld - sorted.

Blasted quotation marks....