vlookup from one workbook to another

[melbridge]

This is my first post - sorry it's so long. Hope it makes sense.

I have to validate the data entered into our database by about 30 staff (officers) in 6 units (teams). To do this the data is exported to a workbook called Complaints.xls. Another workbook (Teams_Master.xls) opens this and runs some code checking the data against certain rules. Once it has done this it saves the Complaints.xls workbook.
One of the things I have to check is that the correct unit code has been entered. To do this a UDF checks the officer initials and unit code in Complaints.xls using vlookup against a table on a sheet called Officers in Teams_Master.xls. Or at least that's what I want it to do!

At the moment it just puts #Value! in all the cells.

Any help to make this work would be greatly appreciated as it's doing my head in!

Thanks, Dean.

This is the function:

Function dean11(rdl46 As String, rdl47 As String)
Dim rdlk As String
Dim rdll As Range
rdll = "='E:\MANMON\TEAMS VALIDATION\Teams Master\TEAMS_Master.xls'!Officers!$A$2:$C$30"
rdlk = WorksheetFunction.VLookup(rdl47, "[TEAMS_Master.xls]Officers!$A$2:$C$30", 3)
If rdl46 = rdlk Then rdlm = "" Else rdlk = "88888888"
dean11 = rdlk

rdl46 is the unit code in Complaints.xls
rdl47 is the officer initials in Complaints.xls
A2:C30 = initials / name / unit *30

This code puts the function in, copies it down and then tidys it up by copy & paste values then delete the origanal.

Sub Macro1()
'
' 
    ChDir "E:\MANMON\TEAMS VALIDATION\Teams Master\xls"
    Workbooks.Open Filename:= _
        "E:\MANMON\TEAMS VALIDATION\Teams Master\xls\Complaints.xls"
 
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "ERROR UNIT CODE"
    Range("J2").Select
' check unit code against officer list
    ActiveCell.FormulaR1C1 = _
        "='E:\MANMON\TEAMS VALIDATION\Teams Master\TEAMS_Master.xls'!dean11(RC[-1],RC[+1])"
    Range("J2").Select
    Columns("J:J").EntireColumn.AutoFit
    Dim howmany As Long
    howmany = WorksheetFunction.CountA(Range("A:A"))
    Set SourceRange = Range("J2")
    Set fillRange = Range("J2:J" & howmany)
    SourceRange.AutoFill Destination:=fillRange
    Columns("J:J").Select
    Selection.Font.ColorIndex = 3
'copy & paste 88888888
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "ERROR UNIT CODE"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=88888888,88888888,"""")"
    Range("K2").Select
    Columns("K:K").EntireColumn.AutoFit
    Set SourceRange = Range("K2")
    Set fillRange = Range("K2:K" & howmany)
    SourceRange.AutoFill Destination:=fillRange
    Range("K2").Select
    Columns("K:K").Select
    Selection.Copy
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("J2").Select
    Columns("J:J").Select
    Selection.Font.ColorIndex = 3
    Range("J2").Select

There is more code after this but it all works, then it saves the Complaints.xls workbook.

 

[melbridge]

Can anyone else see this?
It looks like no views & no replies from here.
Anyone there?

Thanks Dean.

 

[melbridge]

I've tried:

Application.WorkbookFunction.VLookup...
Application.VLookup...
WorkbookFunction.VLookup...

Doesn't make any difference! Code doesnt crash ie bring up the debug dialogue box.

Any advice on how to do vlookup in vba when the lookup data is in another workbook?

Thanks, Dean.

 

[melbridge]

Well - no help here today

I'm off for a long weekend in Dublin <Guinness - yum yum> so will check back here on Tuesday.

Cheers, Dean.