VBA VLOOKUP or INDEX/MATCH returning #VALUE error

[IanWallbridge]

Hi all,

I'm having problems with some VBA script, and would appreciate any feedback on what I may be doing wrong - I've been using VBA for a few years but this is really beyond anything I've tried before.

The relevant sections of code are below, and the scenario is this:

I have a workbook that contains details of all members of staff:myStaffList (this is the active workbook throughout the script), but some data is missing. I also have access to another workbook, which is allocated to a variable: myODCSource. Both workbooks contain a staff id number, which at the outset is 'Number stored as text' in both workbooks.

For every staff member in myStaffList who does not have an email address (or other data), i want to pull this information across from myODCSource. Initially I tried this with Application.Vlookup, and then with IndexMatch, but cannot get either of these to work. The issue is clearly to do with formatting (changing the lookup to refer to text fields - for example looking up first name and returning surname - works straight away) but no matter how I declare the various query elements , or however I set the initial formatting in the two workbooks (Number as text, Number 000000 etc.), I always end up with #VALUE , 0 or a blank.

Can anyone advise where I am going wrong? I've tried to include all relevant code, and cannot include examples of files because they contain personal data, but feel sure that the issue must be either in the queries or the Dim statements. All feedback appreciated :D

Option Explicit
Sub LS_INITIALISE()
On Error Resume Next

' DECLARATIONS
Dim mySIPFinalrowA As Integer
Dim i, As Integer
Dim vlFindValue As Long
Dim vlEmail As Variant
Dim vlTarget As Range

' Populate blank cells with VLOOKUP. 
For i = 2 To mySIPFinalrowA
    If IsEmpty(Range("F" & i)) Then
    vlFindValue = Range("A" & i)
    vlTarget = "[" & myODCSource & "]Sheet1!$F$4:$X$6000"
    vlEmail = Application.VLookup(vlFindValue, vlTarget, 2, False)
    Range("F" & i).Value = vlEmail
    End If
Next i

' Populate blank cells with INDEX MATCH
For i = 2 To mySIPFinalrowA
    If IsEmpty(Range("F" & i)) Then
    vlFindValue = Range("A" & i)
    vlTarget = "[" & myODCSource & "]Sheet1!$F$4:$X$6000"
    vlEmail = Application.Index("[" & myODCSource & "]Sheet1!$G$4:$G$6000", Application.Match(Range("A" & i), "[" & myODCSource & "]Sheet1!$F$4:$F$6000"))
    Range("F" & i).Value = vlEmail
    End If
Next i

' Notes
In myStaffList, column F is email, column A is staff id number
In MyODCsource, data (excluding headers) is in the range F4:X6000, with staff id in column F and email in column G
 
End Sub

 

[strooman]

You can concatenate an empty string to the lookup value, which converts the lookup value to text:

vlEmail = Application.VLookup(vlFindValue&"", vlTarget, 2, False)

 

[sericom]

Your vlTarget line doesn't look right, if myODCSource is a string type, then try

Set vlTarget = Workbooks(myODCSource).Sheets("Sheet1").Range("$F$4:$X$6000")

or if it's a workbook type use

Set vlTarget = myODCSource.Sheets("Sheet1").Range("$F$4:$X$6000")