vlookup requires a range reference as the second argument. You are supplying
a string

tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38"

so change the declaration from
> Dim tmpRange As String

to Dim tmpRange as Range

then do

set tmpRange = "Workbooks("TicketReport.xls") _
.Worksheets("Tmp").Range("I6:J38")

TicketReport.xls will need to be open when you run the code.

--
Regards,
Tom Ogilvy

"SP" <polifemo@suscom.net> wrote in message
news:1129607295.522248.170190@z14g2000cwz.googlegroups.com...
> The following code inserts #VALUE when I attempt to use the
> Application.Vlookup
> function.
> If I mouse over the Cells(dRow, dCol) I see error 2015 in the pop-up
> box.
>
> I am using Excel 2002. Any idea what is wrong ?
>
> Thanks in advance
> Sal
>
> Sub CreateReport()
> 'Declare variables
> Dim strFile As String
> Dim wkbCode As Workbook
> Dim wkbReport As Workbook
> Dim wksCustom As Worksheet
> Dim dCol As Double
> Dim dRow As Double
> Dim tmpString As String
> Dim tmpRange As String
>
>
> Set wkbCode = ThisWorkbook
>
> 'Let user choose workbook to open
> strFile = Application.GetOpenFilename _
> ("Text Files (*.txt),*.txt, Excel Files (*.xls), *.xls", 2)
> If strFile <> "False" Then
> Set wkbReport = Workbooks.Open(strFile)
> End If
>
> 'Display message at bottom of screen
> Application.StatusBar = "Creating the report..."
>
> 'Delete "Category" column
> dCol = Application.Match("Category", Rows(1), 0)
> If Not IsError(dCol) Then
> Columns(dCol).Delete
> End If
>
> 'Insert "Manager" column between "Status" and "Caller" columns
> dCol = Application.Match("Caller", Rows(1), 0)
> If Not IsError(dCol) Then
> Columns(dCol).Insert
> End If
> Cells(1, dCol) = "Manager"
>
>
> 'look up area manager and insert into "Area Mgr" column
> Application.EnableEvents = False
> tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38"
> For dRow = 2 To 100
> tmpString = Cells(dRow, dCol + 1)
> Cells(dRow, dCol) = Application.VLookup(tmpString, tmpRange, 2,
> False)
> Next dRow
> Application.EnableEvents = True
>